pvsbandi
asked on
Delete rows dynamically
Hi,
We are on Postgres 9.6 Linux database.
Have a requirement to create a function and pass cc_case_ID as the parameter.
requirement then, is to to delete all the records from all the tables in a schema, where the cc_case_ID is equal to the parameter value passed.
Can someone kindly help with the solution?
We are on Postgres 9.6 Linux database.
Have a requirement to create a function and pass cc_case_ID as the parameter.
requirement then, is to to delete all the records from all the tables in a schema, where the cc_case_ID is equal to the parameter value passed.
Can someone kindly help with the solution?
you want to create a gig for this???
ASKER
What's a gig?
have you read the plpgsql documentation? what you are asking is not very difficult, but one would need to know how the foreign keys are set up.
ASKER
I understand that there would be challenges with respect to the referential integrity, but would like to get the basic logic working first.
I have created a function, and it compiled fine.
But when i execute it , throws error: Help please?
" ERROR: missing FROM-clause entry for table "a"
Where: PL/pgSQL function delete_rows_individual_id_ lookup(int eger) line 11 at EXECUTE
"
I have created a function, and it compiled fine.
But when i execute it , throws error: Help please?
" ERROR: missing FROM-clause entry for table "a"
Where: PL/pgSQL function delete_rows_individual_id_
"
CREATE OR REPLACE FUNCTION public.delete_rows_individual_id_lookup(v_cs_id INTEGER)
RETURNS VOID AS
$func$
DECLARE
tb_name text;
BEGIN
FOR tb_name IN
SELECT table_name
FROM information_schema.columns
WHERE table_schema = 'public'
AND column_name = 'cs_id'
LOOP
EXECUTE
'DELETE FROM public.'||quote_ident(tb_name) ||
' A WHERE exists (SELECT 1 FROM public.DEL_TEST_MSTR B
where A.ind_id = B.ind_id '
|| CASE WHEN EXISTS (SELECT 1 FROM information_schema.columns B
WHERE A.table_name = B.table_name
AND A.table_name = quote_ident(tb_name)
AND B.column_name = 'cs_id'
)
THEN chr(10)||' and A.cs_id = B.cs_id'
ELSE ''
END
|| chr(10)|| 'and B.cs_id = '||cast(v_cs_id as int)
|| ' )'
||' from information_schema.columns A
where column_name = ''cs_id''';
END LOOP;
END
$func$ LANGUAGE plpgsql;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Could you please give me a simple example code that works?
I will then model my function based in that
I will then model my function based in that
If you set up the relationships correctly, you can delete the row from only the parent table and cascade delete will delete all related child rows.
ASKER
Hi Pat,
We don't want the cascade to be in for all the time.
This deletion effort is only to delete the test cases seeded in production, so will be a one-time effort each time, a release goes out.
We don't want the cascade to be in for all the time.
This deletion effort is only to delete the test cases seeded in production, so will be a one-time effort each time, a release goes out.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.