Postgresql – Resolve parametrized schema in PostgreSQL

functionsgreenplumpostgresql

I'm looking for a way to use a parametrized schema in the DECLARE section of a PostgreSQL function.

Why am I looking this way ?

  1. This functions will refer %ROWTYPE from multiple schema like

Var1 s1.emp%ROWTYPE ===> to refer the columns of emp tables as variable from s1 schema

var2 s2.stg_emp_d%ROWTPE; ===> to refer the columns of stg_emp_d tables from s2 schema as variable

var3 s3.emp_comp%ROWTYPE;===> to refer the colums of emp_comp tables from s3 schema as variable

  1. s1, s2, s3 (schema names) are not same across different env eg:
IN DEV ENV s1, s2, s3
IN TEST ENV s1_t, s2_t, s3_t
IN PROD ENV s1_p, s2_p, s3_p
  1. As we accept schema names as parameter in function, same function can be deployed without any changes across environments. It will inherit appropriate schema name set during run-time.

As we use %ROWTYPE of respective table, any changes to table structure will not impact these function. These changes are inherited via %ROWTYPE.

  1. These functions need to get individual table columns name via %ROWTYPE with in BEGIN section like
if var1.emp_type = 'C' then ... do some thing ;
elsif  var1.emp_type = 'T' then ... do some thing ;

end if ;

Here is an example:

CREATE OR REPLACE FUNCTION get_list(in_code text[], p_schema text)
  RETURNS text  AS
$func$

DECLARE

  var1 user.emp%ROWTYPE;

BEGIN

SELECT q.id, q.title, q.code
FROM   questions q, emp e
WHERE  q.code <> ALL ($1)
and    q.emp_id = e.emp_id;

END ;  

$func$ LANGUAGE sql;

The above getting created.

When I change var1 user.emp%ROWTYPE; to

  • var1 p_schema.emp%ROWTYPE; or
  • var1 $$ || p_schema || $$.emp%ROWTYPE;

Function is not getting created, but throwing error

ERROR : relation emp not found

Are there any limitations using parameterized items within DECLARE section?

I used these kinds of parameters with queries within BEGIN & END section. It did not throw any errors.

Best Answer

The short answer is that your use of variables in that manner in the DECLARE statement isn't going to work. I think the workaround you should shoot for is to use a RECORD type, but your example doesn't really explain the end goal, so it's hard to be 100% certain.

Related Question