Thread: Create a function that creates a function which returns a table and return that table in one step
Create a function that creates a function which returns a table and return that table in one step
From
Rainer Schuetz
Date:
Hello,
I hope this is the right place to post my question, it is a user-problem and I wasn't able to extract a solution from the documentation. I am using PostgreSQL 8.35 I am a real novice in the field. I am running my SQL-queries in pgAdmin and create plpgsql-functions from there as well. I hope I can make my problem understood:
I have a function (the 'outer' function) that creates another function which runs a select-query on a table. This table is specified by a parameter I use when running the outer function (there are many tables, on which the query could be executed and I would like to prevent having to write my function for each of them). The outer function concatenates a query-string by processing parameters I specify when calling the function. As said above one of this parameters is the table-name. Within the outer function I use the parameter-alias 'tablename' to specify the return-type in the created function that returns the table (tablename%rowtype), and as the variable is expanded before the function is created, a working 'inner' function results, where the name of the queried table and return-type fit one-another. As the outer function returns nothing (for now it only creates the function) I specify return-type void.
What I would like to do is not only create the 'inner' function from within the 'outer' function, but run it as well so that the table produced by the inner function is returned right away. From my (minimal) understanding this requires that I specify a return-type in the outer function - yet this return-type could be different each time I run the function because the called table as specified by a parameter could be different each time I create the inner function. If I try to use the parameter-alias 'tablename' like in the inner function (tablename%rowtype) I do get an error:
ERROR: relation "tablename" does not exist
Or:
ERROR: type "tablename" does not exist
If I use SETOF record as return type and declare a variable of the datatype record for the RETURN statement, the function interpreter accepts my function, but when I run the function, I get the error message:
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function "test" line 46 at RETURN NEXT
I tried around with polymorph returntypes like anyelement, but had no luck. Does anybody have an idea, what I am doing wrong and how I should correct this?
I attach the original function, it is quite intransparent, yet hope with above explanation things might be understandable. It is not the inner select-query that causes problems, although it looks strange with all the ''' and concatenation symbols. I can run the resulting inner function manually without problems.
Thank you for any help
Best
Rainer
-----
CREATE OR REPLACE FUNCTION test(tablename text, slpl text, frm text, div text, ost numeric)
RETURNS SETOF record AS $BODY$
DECLARE
createF text;
runF text;
resultData record;
BEGIN
-- prepare inner function:
createF := 'CREATE OR REPLACE FUNCTION FoO() RETURNS SETOF '||tablename||' AS $$
DECLARE
sqlQuery varchar;
output '||tablename||'%rowtype;
BEGIN
sqlQuery := '' SELECT * FROM '||tablename||'
WHERE pathet ~ '||''''''||slpl||''''''||'
AND structure ~ '||''''''||frm||''''''||'
AND (substring(phase::text FROM char_length(phase::text)-7)::numeric + '||ost||') % '||div||' = 0'';
FOR output IN EXECUTE sqlQuery LOOP
RETURN NEXT output;
END LOOP;
RETURN;
END;$$language plpgsql';
-- end inner function definition
-- create inner function (works):
DROP FUNCTION foo();
EXECUTE createF;
-- attempt to run inner function (fails):
FOR resultData IN SELECT * FROM foo() LOOP
RETURN NEXT resultData;
END LOOP;
RETURN;
END$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
Re: Create a function that creates a function which returns a table and return that table in one step
From
Tom Lane
Date:
Rainer Schuetz <rs@bagong.de> writes:
> I have a function (the 'outer' function) that creates another function
> which runs a select-query on a table. This table is specified by a
> parameter I use when running the outer function (there are many
> tables, on which the query could be executed and I would like to
> prevent having to write my function for each of them).
Introducing the extra function seems to be just complicating your life.
Why don't you just EXECUTE the desired SELECT directly from the main
function?
regards, tom lane