postgres function cannot see variable

Question

I have function which supposed to create missing objects. I have a variable that I am using to set active schema tschem_name

However feels like proc cannot see this variable if it is trying to create this object, however, if which check if object exists can see this variable feels like it treats it as a column from the table. Any idea how to work around it? I was trying to use declare inside else statement but it doesn't work :(

ERROR: column "tschem_name" does not exist

CREATE OR REPLACE FUNCTION public.fn_upd_schema(
                                                tschem_name character varying
                                               )
    RETURNS character varying
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE

    --tname text := t_shema;
    --tschem_name text := tname||'_assets';
    tsql_dyn text ;
    nschema_check numeric := 0 ;

 BEGIN

    SELECT 1 
      INTO nschema_check
      FROM PG_NAMESPACE
     WHERE NSPNAME = tschem_name;

 IF nschema_check  = 0 THEN 
 
    RETURN 'Schema '||tschem_name ||' Does Not Exist';
    
 ELSE 
    tsql_dyn := 'SET search_path TO  '||tschem_name ||';';
    raise notice 'EXECUTE %', tsql_dyn;
    EXECUTE tsql_dyn;
IF EXISTS (SELECT FROM information_schema.tables 
                  WHERE table_schema = tschem_name
                    AND table_type   = 'VIEW'
                    AND table_name   = 'vw_aaa') 
        THEN
        null;--  raise notice 'vw_aaa EXISTS';
        ELSE

        --declare tschemaname text := tschem_name;
        
            CREATE VIEW vw_aaa AS
            SELECT 
               tschem_name::text AS database,
tale1.column1,
tale1.column2
From table1;
endif;
RETURN tschem_name ||' UPDATED TO STANDART';

END IF;

 END;
$BODY$;

Laurenz Albe · Accepted Answer · 2021-10-20 06:16:52Z

1

The problem is your CREATE VIEW statement. You need to use dynamic SQL with EXECUTE for that.

The underlying reason is that CREATE VIEW does not accept parameters.

By the way, your code is vulnerable to SQL injection. Never construct an SQL statement by concatenating strings with ||. Use format with the %I placeholder.

edited Oct 20, 2021 at 6:16

answered Oct 20, 2021 at 4:33

Laurenz Albe

257k22 gold badges310 silver badges387 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andrey Over a year ago

yeah, I was thinking about dynamic, but why it doesn't recognize the variable, in Tsql it will work as it's just simple text why in Postgres it doesn't work? I couldnt. find the answer in the documentation.

Laurenz Albe Over a year ago

I have added an explanation. You have to get used to the fact that different RDBMS work differently.

Andrey Over a year ago

Thanx mate, I am from the TSQL world and simply didn't know that I cannot use variables directly in the DDL statement in Postgres :)

Collectives™ on Stack Overflow

postgres function cannot see variable

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related