0

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?

Improve this question
4
  • 1
    To help people answer your question, you'll need to be more specific about the error. Please edit your post to incorporate the exact errors you get from your minimal reproducible example (preferably using copy+paste to avoid transcription errors). Commented Feb 7, 2018 at 11:47
  • 1
    Which environment? Commented Feb 7, 2018 at 11:47
  • 1
    What programming language are you using? In PL/pgSQL you only use execute: postgresql.org/docs/current/static/… Commented Feb 7, 2018 at 11:54
  • Watch out for SQL injection! Commented Feb 7, 2018 at 15:59

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

In prepare str command I cannot prepare sql from string. Also, Can you please tell me How can I return this in function as cursor. I updated my question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.