Resolve parametrized schema in PostgreSQL
1
vote
2
answers
1222
views
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
2) 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
`
3) 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.
4) 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.
Asked by user2647763 - RIMD
(11 rep)
Sep 12, 2020, 02:09 AM
Last activity: Dec 19, 2024, 04:40 AM
Last activity: Dec 19, 2024, 04:40 AM