2

I'd like to create an SQL file, where the user can just change variables on the beginning , and use it in different configuration system. Typically for load layer from different PostGIS server with postgres_fdw.

I tried it with PL/pgSQL where I can declare variables before beginning processing, but I don't no how to use them after...

DO $$
DECLARE  
    url character := '193.00.00.00';  
    pt character := '5432';  
    db character := 'bd_name';  
    usr character := 'user_name';
    pwd character := 'password';
BEGIN  
    CREATE EXTENSION IF NOT EXISTS postgres_fdw;

    CREATE SERVER foreign_srv
        FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (host url, port pt, dbname db);

    CREATE USER MAPPING FOR USER
        SERVER foreign_srv
        OPTIONS (user usr, password pwd);

END $$;

It obviously doesn't work like that.

  • This shows how to use the variables postgresqltutorial.com/plpgsql-variables – Mapperz Oct 9 '19 at 16:14
  • Do you want the users to be running pl/pgsql or do you want them to use standard sql queries? common table expressions could be used to accomplish this using standard SQL – DPSSpatial Oct 9 '19 at 16:49
3

What if you tried the EXECUTE command:

EXECUTE 'CREATE SERVER foreign_srv
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host $1, port $2, dbname $3)' USING url, pt, db;

https://www.postgresql.org/docs/9.1/plpgsql-statements.html

There are a bunch of different ways this execute command can be used. Variations of format, %s, %L, etc.. You'll have to look up how to implement in your case. Here's another example on how to use for reference:

DO $$ 
DECLARE  
    db character varying := 'D_C';
    a character varying := 'test_char1';  
    b character varying := 'test_int';  
    c character varying := 'test_char2';
BEGIN  
    EXECUTE format('CREATE table %s.cls_del (%s character varying, %s integer, %s double precision)', db, a, b, c);
END $$;
  • It give me an error : postgres error syntax error at or near "$1" Context : fonction "PL/pgsql inline_code_block, line 11 at EXECUTE – Zacharie Moulin Oct 10 '19 at 9:53
1

You can use EXECUTE command with a concatenated string (with USING like @D_C said, or like the following), but don't forget these parameters need to be encapsulated with '', so you need to escape with \' if you are already in a string:

EXECUTE 'CREATE SERVER foreign_srv
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host \''||url||'\', port \''||pt||'\', dbname \''||db||'\')';

It should work with using? :

EXECUTE 'CREATE SERVER foreign_srv
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host \'$1\', port \'$2\', dbname \'$3\')' USING url, pt, db;
  • Thanks for insert method with ||, it's work. But \' don't work. I've fund the solution by use '||quote_literal(variable)||' – Zacharie Moulin Oct 10 '19 at 11:38
  • Strange, I though it should work... Anyway your solution is way better! There is also the $$ string apparently, as another solution (stackoverflow.com/questions/12316953/…) – robin loche Oct 10 '19 at 12:07
1

I've found solution with '||quote_literal(variable)||'. For my example :

DO $$
DECLARE  
    url character := '193.00.00.00';  
    pt character := '5432';  
    db character := 'bd_name';  
    usr character := 'user_name';
    pwd character := 'password';
BEGIN  
    CREATE EXTENSION IF NOT EXISTS postgres_fdw;

    EXECUTE 'CREATE SERVER foreign_bd_ign 
    FOREIGN DATA WRAPPER postgres_fdw 
    OPTIONS (host '||quote_literal(url)||', port '||quote_literal(pt)||', dbname '||quote_literal(db)||')';

    EXECUTE 'CREATE USER MAPPING FOR 
    USER SERVER foreign_bd_ign 
    OPTIONS (user '||quote_literal(usr)||', password '||quote_literal(pwd)||')';

END;
$$;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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