CODEWITHSUNDEEP
sqlpostgresqlfunctionplpgsqldynamic-sql
Martial P
Martial P

Reputation: 395

How do I execute a SELECT query in a text string?

I have a function my_funct(param1,param2) returning a SELECT QUERY as a text, I usually C/c the result, remove quotes, then run it.

How can execute the returned text in a query that call the function ?

I expect something like this:

    SELECT resultOf( myfunct('foo','bar'))

with no extra columnname/type to declare. If such function does not exits built-in, let's create it, I don't mind.

Upvotes: 1

Views: 994

Answers (1)

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6130

If I understood correctly you want

  • Call a function returning a select query as text
  • Run that query to get the result.
  • Number and type of columns are dynamic

1. Using Do Block:

DO
$$
declare 
ref_cursor refcursor:='mycursor';
begin
open ref_cursor for execute (select * from my_funct('foo','bar')) ;
end;
$$;

--Fetch all data from cursor
Fetch all from mycursor;

2. Using Function returning refcursor:

You can create a function like below:

create function my_funct1(param1 text) returns refcursor as

$$
declare 
ref_cursor refcursor:='mycursor';
begin
open ref_cursor for execute param1;
return ref_cursor;
end;
$$
language plpgsql

To call above function use following code:

begin ;
select my_funct1((select * from my_funct('foo','bar')) );
fetch all from mycursor;
commit;

DEMO

Upvotes: 2

Related Questions