Forum OpenACS Q&A: Postgress plpgsql question

Collapse
Posted by Steve Manning on
I'm hoping someone can clarify this for me. In a plpgsql function what is the purpose of 2 apostrophies instead of 1?

e.g

INSERT INTO location_county VALUES (nextval(''county_seq''),p_name);

works but,

INSERT INTO location_county VALUES (nextval('county_seq'),p_name);

gives and error 'Unable to identify a function that satisfies the given argument types'.

TIA
    Steve

Collapse
Posted by Roberto Mello on
In PG functions, the whole body of the function is enclosed in  apostrophes, so you have to escape then within the function body.

It can get pretty nasty, so I wrote a little table about that when I revamped the PL/pgSQL documentation and wrote the oracle PL/SQL porting guide that are part of the PostgreSQL documentation:

http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=plpgsql.html

http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=plpgsql-porting.html

-Roberto

Collapse
Posted by Vinod Kurup on
Hi Steve,

Since the body of a PLPGSQL function is enclosed in single quotes, any single quotes within the function must themselves be quoted. Basically, wherever you see a single quote in a non-plpgsql statement, you need 2 in a plpgsql statement.

See Roberto's guide for examples of how complex this can get 😊

Collapse
Posted by Vinod Kurup on
damn - foiled by roberto's quick fingers again!
Collapse
Posted by Steve Manning on
Thanks guys - makes perfect sense now you've pointed out the wrapping quotes.

And thanks Roberto for the excellent guide which I shall read in bed tonight (actually thats probably more detail than you required :)