Postgres run SQL statement from string

Question

I would like to execute a SQL statement based on a string. I have created the SQL statement through a function which returns a string. Can anybody explain how I can execute the statement that is returned? I know that you can't do it in plain SQL, so I was thinking about putting it in a function. The only issue is that the columns in the statement aren't always the same, so I don't know which data types to use for the columns. I'm using Postgres 9.1.0.

For example, suppose the SQL string returned from my function the is:

Select open, closed, discarded from abc

But, it can also be: Select open from abc

Or Select open, closed from abc

How can I execute any of these strings, so that the results would be returned as a table with only the columns listed in the statement?

Edit: the function is written in PL/pgSQL. And the results will be used for reporting where they don't want to see columns that have no values. So the function that I wrote returns the names of all columns that have values and then add it to the SQL statement.

Thanks for your help!

When you say "function," do you mean a function in, for example, PL/pgSQL, or do you mean a function in your general purpose programming language? If the latter, we will need to know the language and any library you are using to interact with postgres. You can click on "edit" to add more information to your question. Thanks! — Wayne Conrad, Jun 26 '14 at 19:14
It might be useful if you explain your real use case. Why you need such thing? Just to display data on your screen? Or pass data somewhere, export it, wrap it in another statement, create another table? — Tomas Greif, Jun 26 '14 at 19:19
and how are you connected to the database? or do you want to execute psql on say a command line and pass your query to it? — Doon, Jun 26 '14 at 19:28
If you have names of columns with no values available, wouldn't it be better to filter these out in presentation layer? Most reporting engines have some kind of scripting capability. — Tomas Greif, Jun 26 '14 at 19:38
You could select the results into a temporary table, then drop columns from the temporary table: http://stackoverflow.com/a/1712243/238886 . It'll be slow and ugly, but I don't think there will be any pretty way to do what you're asking. — Wayne Conrad, Jun 26 '14 at 19:40
I'm using pgAdmin III. Even if I were to use a temp table, how can I select the results from the select statement? Using my example above, the output of my function is a select statement (SELECT open, closed FROM abc). How can I have Postgres execute that select statement to get the results that I need? I see that it should be possible, but I can't determine what the data types should be for each column because I don't know which will be included in the results. — user3780646, Jun 27 '14 at 11:21

score 0 · Answer 1 · answered Jul 15 '14 at 12:55

I don't think you can return the rows directly from a function, because its return type would be unknown. Even if you specified the return type as RECORD, you'd have to list the returned columns at call time. Based on Wayne Conrad's idea, you could do this:

CREATE FUNCTION my_create(cmd TEXT) RETURNS VOID AS $$
    BEGIN
        EXECUTE 'CREATE TEMPORARY TABLE temp_result AS ' || cmd;
    END;
$$ VOLATILE LANGUAGE plpgsql;

Then use the function like this:

BEGIN;
SELECT my_create(...);
SELECT * FROM temp_result;
ROLLBACK; -- or COMMIT

Postgres run SQL statement from string

1 Answers1