Thread: Quoted identifiers in queries in plpgsql functions.
How do I specify quoted identifiers in plpgsql functions? If the following is in test.sql: -- begin test.sql CREATE TABLE Control ( "Full Name" varchar(255) NOT NULL PRIMARY KEY, "Telephone Number" varchar(255) ); INSERT INTO Control ("Full Name","Telephone Number") VALUES ('neal','730-7888'); CREATE OR REPLACE FUNCTION test() RETURNS varchar(255) AS ' DECLARE val varchar(255); BEGIN SELECT INTO val "Telephone Number" FROM Control WHERE "Full Name"=''neal''; RETURN val; END; ' LANGUAGE 'plpgsql'; -- end test.sql I get syntax errors when I try to execute the function: BaseContacts_Devel=# \i test.sql . . . BaseContacts_Devel=# select test(); NOTICE: plpgsql: ERROR during compile of test near line 4 ERROR: unterminated " in name "Telephone I've tried as many combinations of escaping as I can think of, but nothing has worked for me so far. I'll be grateful for any help. thanks neal -- Neal Holtz http://www.docuweb.ca/~nholtz Dept. of Civil and Environmental Engineering, Carleton University, Ottawa, Ontario, Canada K1S 5B6. nholtz@docuweb.ca
On Wed, 19 Jun 2002 08:46:21 -0400 Neal Holtz <nholtz@docuweb.ca> wrote: I would think there is no problem of the singlequote/doublequote in your function. This syntax error depends that plpgsql is unable to regard the space as the part of the column name. Generally, the way to use some space within column names is bad -- at least, is not good. If you can recreate the tables, the 100-percent certain way is to use underlines instead of spaces. > How do I specify quoted identifiers in plpgsql functions? > > If the following is in test.sql: > > -- begin test.sql > CREATE TABLE Control ( > "Full Name" varchar(255) NOT NULL PRIMARY KEY, > "Telephone Number" varchar(255) > ); > > INSERT INTO Control ("Full Name","Telephone Number") VALUES > ('neal','730-7888'); > > CREATE OR REPLACE FUNCTION test() RETURNS varchar(255) AS ' > DECLARE > val varchar(255); > BEGIN > SELECT INTO val "Telephone Number" FROM Control WHERE "Full > Name"=''neal''; > RETURN val; > END; > ' LANGUAGE 'plpgsql'; > -- end test.sql > > I get syntax errors when I try to execute the function: > > BaseContacts_Devel=# \i test.sql > . . . > BaseContacts_Devel=# select test(); > NOTICE: plpgsql: ERROR during compile of test near line 4 > ERROR: unterminated " in name "Telephone > > I've tried as many combinations of escaping as I can think of, > but nothing has worked for me so far. > > I'll be grateful for any help. > > thanks > neal > > > -- > Neal Holtz http://www.docuweb.ca/~nholtz > Dept. of Civil and Environmental Engineering, Carleton University, > Ottawa, Ontario, Canada K1S 5B6. nholtz@docuweb.ca > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster Regards, Masaru Sugawara
To maintain some compatibility with some non-Postgresql applications, we thought it would be useful to retain the spaces in the column names. And in fact, that works just fine in normal queries, but not from within function bodies. Its just that functions are inconsistent in that respect, and I haven't yet found a way to keep the blanks in the names. Its almost as if the tokenizer, or something, is handling the quotes differently, so in that respect, you are correct -- the problems are with the blanks and not the quotes. Had I known there was a problem from the start of the project, I may not have kept the blanks in the names, but now I would prefer not to reverse that decision, if I don't have to. nh On Thu, Jun 20, 2002 at 01:42:37AM +0900, Masaru Sugawara wrote: > On Wed, 19 Jun 2002 08:46:21 -0400 > Neal Holtz <nholtz@docuweb.ca> wrote: > > > I would think there is no problem of the singlequote/doublequote > in your function. This syntax error depends that plpgsql is unable to > regard the space as the part of the column name. Generally, the way > to use some space within column names is bad -- at least, is not good. > If you can recreate the tables, the 100-percent certain way is to use > underlines instead of spaces. > > > > How do I specify quoted identifiers in plpgsql functions? > > > > If the following is in test.sql: > > > > -- begin test.sql > > CREATE TABLE Control ( > > "Full Name" varchar(255) NOT NULL PRIMARY KEY, > > "Telephone Number" varchar(255) > > ); > > > > INSERT INTO Control ("Full Name","Telephone Number") VALUES > > ('neal','730-7888'); > > > > CREATE OR REPLACE FUNCTION test() RETURNS varchar(255) AS ' > > DECLARE > > val varchar(255); > > BEGIN > > SELECT INTO val "Telephone Number" FROM Control WHERE "Full > > Name"=''neal''; > > RETURN val; > > END; > > ' LANGUAGE 'plpgsql'; > > -- end test.sql > > > > I get syntax errors when I try to execute the function: > > > > BaseContacts_Devel=# \i test.sql > > . . . > > BaseContacts_Devel=# select test(); > > NOTICE: plpgsql: ERROR during compile of test near line 4 > > ERROR: unterminated " in name "Telephone > > > > I've tried as many combinations of escaping as I can think of, > > but nothing has worked for me so far. > > > > I'll be grateful for any help. > > > > thanks > > neal > > > > > > -- > > Neal Holtz http://www.docuweb.ca/~nholtz > > Dept. of Civil and Environmental Engineering, Carleton University, > > Ottawa, Ontario, Canada K1S 5B6. nholtz@docuweb.ca > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > > > > Regards, > Masaru Sugawara > > -- Neal Holtz http://www.docuweb.ca/~nholtz Dept. of Civil and Environmental Engineering, Carleton University, Ottawa, Ontario, Canada K1S 5B6. nholtz@docuweb.ca
"Neal M. Holtz" <nholtz@docuweb.ca> writes: > To maintain some compatibility with some non-Postgresql applications, > we thought it would be useful to retain the spaces in the column > names. And in fact, that works just fine in normal queries, > but not from within function bodies. Yeah, the plpgsql lexer has an extremely lame approach to handling double-quoted identifiers (AFAICT, it just acts as though " is an ordinary identifier character :-(). I have a todo item to fix that someday, if no one gets around to it before I do. It should be updated to use the same lexing rules the main SQL lexer does. regards, tom lane