1

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.

1
  • Fix your ENV split instead of inventing hacks. Use several different base names, but with completely identical structure.
    – Melkij
    Apr 15 at 8:44

1 Answer 1

0

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.

1
  • Thanks for your alternatives. I added more info to my questions. I find RECORD type's column name, data type will be exposed upon executing query or For loop with in BEGIN section. In my situation,I need the column names to assign values coming from parameter array. Sep 13, 2020 at 18:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.