Postgres set working schema from inside of function

Question

I am facing a problem I have a function which used to create schema and tables inside that schema after table creation I am calling a function which supposed to populate this schema however feels like the second function doesn't set the working schema and throws the error that object doesn't exits (

ERROR:  relation "table" does not exist
LINE 1: INSERT INTO table

here is what the function looks like.

    CREATE OR REPLACE FUNCTION create_schema(
        t_shema character varying,
        t_country TEXT
        )
        RETURNS character varying
        LANGUAGE 'plpgsql'
        COST 100
        VOLATILE PARALLEL UNSAFE
    AS $BODY$
    DECLARE
    
        tname text := t_shema;
        tschem_name text := tname||'_work';
        tsql_dyn text ;
        tschema_check numeric := 0 ;
    
     BEGIN
    
        SELECT 1 
          INTO TSCHEMA_CHECK
          FROM PG_NAMESPACE
         WHERE NSPNAME = TSCHEM_NAME;
    
     IF TSCHEMA_CHECK  = 1 THEN 
     
        RETURN 'Schema '||tschem_name ||' Already exists';
        
     ELSE 
        tsql_dyn := 'CREATE SCHEMA '||tschem_name||';';
        raise notice 'EXECUTE %', tsql_dyn;
            EXECUTE tsql_dyn;
    
        tsql_dyn := 'SET search_path TO  '||tschem_name ||';';
        raise notice 'EXECUTE %', tsql_dyn;
            EXECUTE tsql_dyn;
    
    
    --other DDLs
---execute of function which populates freshly created schema

    SELECT public.populate_empty_schema(tname, t_country);
    
    RETURN tname ||' created';
    
    END IF;
    
     END;
    $BODY$;

The second function also has a statement as first which sets the working schema. Both functions work fine if get called separately, trows error only if second get called from the first

Laurenz Albe · Accepted Answer · 2022-02-04 05:04:17Z

2

Your function is vulnerable to SQL injection.

Instead of

tsql_dyn := 'CREATE SCHEMA '||tschem_name||';';

write

tsql_dyn := format('CREATE SCHEMA %I', tschem_name);

To set the search_path in populate_empty_schema, pass the schema name to the function and have it execute

PERFORM set_config('search_path', v_schema, TRUE);

answered Feb 4, 2022 at 5:04

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

Andrey Over a year ago

PERFORM set_config('search_path', v_schema, TRUE); in second function still doesn't work, still same error as it doesn't see working schema

Laurenz Albe Over a year ago

Well, you never showed the second function. It should work.

Andrey · Accepted Answer · 2022-02-07 19:39:54Z

0

feels like Postgres cannot set working schema from the second function, my solution. was just deleting the set search path from the second function and it is working. feels like it couldn't set it as shcema is not committed at the point when the 2-nd function gets called.

answered Feb 7, 2022 at 19:39

Andrey

1,77914 gold badges40 silver badges75 bronze badges

2 Comments

Laurenz Albe Over a year ago

There must be a different reason, but I can't tell what's going on.

Andrey Over a year ago

@LaurenzAlbe probably but I couldn't find it, so at least it is working :)

Collectives™ on Stack Overflow

Postgres set working schema from inside of function

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related