CODEWITHSUNDEEP
postgresql
user40739
user40739

Reputation: 91

Executing query as string variable and storing in a table

I have a simple code that works as follows. I have a table A and I want to query it and store the result in a new table B. For simplicity assume qty and price are two of the fields of the table A. My simple sql file contains the following and works just fine.

DROP TABLE IF EXISTS B;
CREATE TABLE B AS

SELECT *
FROM A
WHERE A.qty >10

Now I want to dynamically modify the query by representing it as a string variable (say s) and updating the string s on the fly. Suppose the string variable s is originally

s = 'SELECT * FROM A WHERE A.qty>10'

we then update it on the fly by concatenating another string as follows:

s = 'SELECT * FROM A WHERE A.qty>10 AND A.price >30'

I want to execute s as a query and save the results in Table B.

I read dynamic sql related documents of postgresql and not quite sure how to achieve the above goal. I am novice and any help will be greatly appreciated.

Upvotes: 1

Views: 791

Answers (1)

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6130

You can write a function like this and pass the condition as parameter:

create or replace function add_table_b(s text) returns int as $$

declare
rc int:=0;
query_ text:='';

begin

if(trim(s)= '') then
s='';
else
s=' where '||s;
end if;


DROP TABLE IF EXISTS B;
execute 'CREATE TABLE B AS SELECT * FROM A '|| s;
GET DIAGNOSTICS rc = ROW_COUNT;

return rc;

end;
$$
language plpgsql

this function will return the count of rows inserted in table B.

Upvotes: 1

Related Questions