Geek Answers Handbook

Is there any escaping syntax for the psql variable inside PostgreSQL functions?

I am writing a PSQL script and using variables (for psql -variable key = value command line syntax).

This works fine for a top-level area, for example, select * from: key, but I create functions with a script and need a variable in them.

So the syntax is kind of

create function foo() returns void as
$$
declare
begin
    grant select on my_table to group :user;
end;
$$
language plpgsql;

crash: user.

As far as I understand psql variables, this is a simple macro replacement function, but it does not process function bodies. Is there any syntax for such cases? Environment: a user with $$ is working on a replacement, but psql is not working on $$.

Is there any other way to do this other than offline macro processing (sed, awk, etc.)?

+5
2

PSQL SET . , , , SET .

, :user PL/pgSQL, . , , ... , , . , .

, . ( ): , PL/pgSQL. .

CREATE OR REPLACE FUNCTION foo("user" TEXT) RETURNS void AS
$$
BEGIN
        EXECUTE 'GRANT SELECT ON my_table TO GROUP ' || quote_ident(user);
END;    
$$ LANGUAGE plpgsql;

:

  • EXECUTE GRANT , . PG " " EXECUTE.
  • user . .

​​, , PSQL. .

  • psql --variable user="'whoever'" --file=myscript.sql. !
  • myscript.sql , .
  • myscript.sql select foo(:user);. , user.

, , , , . , SET . SET .

: SET. manpage: " , , , ". Postgres user ( ), script , . , psql user - , SET . !

+7

, Dan LaRocque, ( ) - psql ( , - ). , , :

create function foo(v_user text) returns void as
$$
begin
    execute 'grant select on my_table to group '||quote_ident(v_user);
end;
$$
language plpgsql;

(quote_ident() , , .)

, Perl, Python, Ruby .., Postgres .

Psql , , .

+4

More articles:


All Articles