CODEWITHSUNDEEP
sqlpostgresql
Vishal D
Vishal D

Reputation: 51

How to run a query from a string in postgresql?

I want to run a query which is saved in a string, for example: str='select 753 as number'

I tried exec sql but am getting this error:

EXEC SQL EXECUTE IMMEDIATE str

Also I am using it in a postgresql function. SO I have a dynamic query in a string. How can I run query in a string which can be returned as cursor. Something like this:

    prepare strg as 'select 7777 as number';
    open fcur for execute strg;

can it be done?

Upvotes: 0

Views: 2779

Answers (1)

SovereignSun
SovereignSun

Reputation: 202

You cannot execute a string which returns rows with "EXECUTE IMMEDIATE string":

EXECUTE IMMEDIATE immediately prepares and executes a dynamically specified SQL statement, without retrieving result rows. (Source)

You can however execute a command that a variable hosts:

str='Delete * from table01'
EXEC SQL EXECUTE IMMEDIATE :str;

However you can use Prepare and Execute:

prepare str as select 753 as number;
execute str;

To select into a cursor, first open cursor and then select data into it.

Upvotes: 1

Related Questions