Forum OpenACS Q&A: Postgress plpgsql question

Collapse
1: Postgress plpgsql question
Posted by Steve Manning on 04/01/03 10:50 PM
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
2: Re: Postgress plpgsql question (response to 1)
Posted by Roberto Mello on 04/01/03 11:17 PM
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
3: Re: Postgress plpgsql question (response to 1)
Posted by Vinod Kurup on 04/01/03 11:21 PM
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
4: Re: Postgress plpgsql question (response to 1)
Posted by Vinod Kurup on 04/01/03 11:25 PM
damn - foiled by roberto's quick fingers again!
Collapse
5: Re: Postgress plpgsql question (response to 1)
Posted by Steve Manning on 04/01/03 11:29 PM
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 :)