Call Function From Different Schema Dynamically

[gwinn7]

How would one construct PL code to call a function within a function dynamically?

I already have a method for doing this, but in my opinion, there has got to be a better way. Example...

CREATE OR REPLACE FUNCTION public.myfunction(schemaname text)
RETURNS int4 AS
$$
DECLARE
myrec record;
myval1 int4;
BEGIN

FOR myrec IN EXECUTE
'SELECT "' || myschema || '".myfunctioncall()
as myresult
FROM "' || myschema || '".mytable'
LOOP
END LOOP;

myval1 = myval1 + myrec.myresult;

RETURN myval1;

END; $$ LANGUAGE plpgsql;

As you can see, its a mess, but it will work. I am forced to use the FOR LOOP because the SELECT INTO does not support dynamically constructed string statements, and I am forced into using a SELECT statement and a FROM clause to call my function in the specified schema.

Any insight? Perhaps, I am just missing something really simple?

"Spock, help me..SPOCK!" -- Star Trek (original series)

Gary
gwinn7

 

[ceco]

maybe something like

EXECUTE 'set search_path TO ' || myschema;

see

http://www.postgresql.org/docs/8.1/interactive/ddl-schemas.html#DDL-SCHEMAS-PATH

for details

 

[gwinn7]

Interesting idea, but wouldn't this affect other users during processing?

Say if a user invoked functions that had that code, this could potentially affect other users calling the same functions? Wouldn't there be a conflict by constantly setting the search_path?

Gary

 

[ceco]

no the other user will not be affected

only the connection in which you execute set search_path is affected by it

I mean only the consecutive queries on the same connection may be affected, but you can after that set the search path to its normal situation

 

[gwinn7]

Ceco,

I just converted all my code to utilize your suggestion. It seems to work great!

Thank you Very Much!

Gary