postgres function cannot see variable-CodePudding

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$;

CodePudding user response：

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.