How to execute string result of stored procedure in postgres
I created the following stored procedure which basically gets the table name and prefix. The function then finds all columns that use this prefix and returns the "select" ("myoneliner") query command as output. in the following way:
CREATE OR REPLACE FUNCTION mytext (mytable text, myprefix text)
RETURNS text AS $myoneliner$
declare
myoneliner text;
BEGIN
SELECT 'SELECT ' || substr(cols,2,length(cols)-2) ||' FROM '||mytable
INTO myoneliner
FROM (
SELECT array(
SELECT DISTINCT quote_ident(column_name::text)
FROM information_schema.columns
WHERE table_name = mytable
AND column_name LIKE myprefix||'%'
order by quote_ident
)::text cols
) sub;
RETURN myoneliner;
END;
$myoneliner$ LANGUAGE plpgsql;
Call:
select mytext('dkj_p_k27ac','enri');
As a result of running this stored procedure and selecting "select" following it, I get the following output in the Data Output window (all inside a single cell called "text text"):
'SELECT enrich_d_dkj_p_k27ac,enrich_lr_dkj_p_k27ac,enrich_r_dkj_p_k27ac
FROM dkj_p_k27ac'
I would like to basically take the command line output I received as output and execute it. In other words, I would like to be able to and execute the output of my stored procedure. How can i do this?
I tried the following:
CREATE OR REPLACE FUNCTION mytext (mytable text, myprefix text)
RETURNS SETOF RECORD AS $$
declare
smalltext text;
myoneliner text;
BEGIN
SELECT 'SELECT ' || substr(cols,2,length(cols)-2) ||' FROM '||mytable
INTO myoneliner
FROM (
SELECT array(
SELECT DISTINCT quote_ident(column_name::text)
FROM information_schema.columns
WHERE table_name = mytable
AND column_name LIKE myprefix||'%'
order by quote_ident
)::text cols
) sub;
smalltext=lower(myoneliner);
raise notice '%','my additional text '||smalltext;
RETURN QUERY EXECUTE smalltext;
END;
$$ LANGUAGE plpgsql;
Call function:
SELECT * from mytext('dkj_p_k27ac','enri');
But I am getting the following error, can you please advise what should I change to make it work?
ERROR: a column definition list is required for functions returning "record"
LINE 26: SELECT * from mytext('dkj_p_k27ac','enri');
********** Error **********
ERROR: a column definition list is required for functions returning "record"
SQL state: 42601
Character: 728
source to share
Your first problem was solved with dynamic SQL with EXECUTE
as Craig recommended. But the rabbit hole goes deeper:
CREATE OR REPLACE FUNCTION myresult(mytable text, myprefix text)
RETURNS SETOF RECORD AS
$func$
DECLARE
smalltext text;
myoneliner text;
BEGIN
SELECT INTO myoneliner
'SELECT '
|| string_agg(quote_ident(column_name::text), ',' ORDER BY column_name)
|| ' FROM ' || quote_ident(mytable)
FROM information_schema.columns
WHERE table_name = mytable
AND column_name LIKE myprefix||'%'
AND table_schema = 'public'; -- schema name; might be another param
smalltext := lower(myoneliner); -- nonsense
RAISE NOTICE 'My additional text: %', myoneliner;
RETURN QUERY EXECUTE myoneliner;
END
$func$ LANGUAGE plpgsql;
Highlights
-
Do not cast the entire operator to lowercase. Column names can be double quotes with uppercase letters, which are case sensitive in this case (no pun intended).
-
You don't need
DISTINCT
to request forinformation_schema.columns
. Column names are unique for each table. -
You do have to specify the schema though (or use a different way to highlight the same schema), or you can mix column names from multiple tables with the same name in multiple schemas, leading to nonsense.
-
You should disinfect all identifiers in a dynamic code, including the names of the tables:
quote_ident(mytable)
. Keep in mind that your text function parameter is case sensitive! The request forinformation_schema.columns
also requires this. -
I was unraveling your whole construct to create a list of column names using an
string_agg()
array constructor instead. Related answer: -
Simplified syntax
RAISE NOTICE
.
The main problem that cannot be solved
All this will not solve your main problem yet. : SQL requires the definition of the returned columns. You can get around this by returning anonymous entries as you tried. But it just postpones the inevitable. Now you need to provide a list of column definitions during the conversation , just like the error message. But you just don't know which columns will be returned. Catch 22.
Your call will work like this:
SELECT *
FROM myresult('dkj_p_k27ac','enri') AS f (
enrich_d_dkj_p_k27ac text -- replace with actual column types
, enrich_lr_dkj_p_k27ac text
, enrich_r_dkj_p_k27ac text);
But you don't know the numbers, names (optional), and data types of the returned columns, not the time the function was created, or even at the time of the call. It is impossible to do exactly that in one call. The database needs two separate queries .
You could dynamically return all columns of any given table using the polymorphic type function , because there is a well-defined type for the entire table. Last chapter of this related answer:
source to share