Thread: Unexpected Return from Function
I have a FUNCTION:
CREATE FUNCTION get_responsible(text)
RETURNS TEXT AS '
DECLARE responsible_list text; my_record RECORD;
BEGIN FOR my_record IN SELECT login FROM employee WHERE id IN (1,2) LOOP responsible_list := responsible_list || '',
''my_record.login; END LOOP; RETURN responsible_list;
END;
' LANGUAGE 'plpgsql';
The employee table is such:
id | login
-------------
1 | anthony
2 | mary
-------------
I expect the SQL statement "SELECT get_responsible('1,2')" to return
something like so:
get_responsible
---------------
anthony, mary
---------------
But instead I receive:
get_responsible
---------------
---------------
If I get rid of the concatenation, like so:
CREATE FUNCTION get_responsible(text)
RETURNS TEXT AS '
DECLARE responsible_list text; my_record RECORD;
BEGIN FOR my_record IN SELECT login FROM employee WHERE id IN (1,2) LOOP responsible_list := my_record.login; END
LOOP;RETURN responsible_list;
END;
' LANGUAGE 'plpgsql';
I receive last result (for id = 2), like so:
get_responsible
---------------
mary
---------------
The SELECT statement itself runs fine, so I know it is returning two
records.
Also, the reason I am passing a 'text' datatype to the function, is
because I'd ultimately like to have the "WHERE id IN" statement to be
dynamic, like so:
CREATE FUNCTION get_responsible(text)
RETURNS TEXT AS '
DECLARE responsible_list text; my_record RECORD;
BEGIN FOR my_record IN SELECT login FROM employee WHERE id IN ($1) LOOP responsible_list := my_record.login; END
LOOP;RETURN responsible_list;
END;
' LANGUAGE 'plpgsql';
If anyone can help me with this, I'd be much appreciative. I've been
trying combination after combination of things to try and resolve this
for the past 6 and a half hours.
Thanks,
Anthony "pulling his hair out" Bouvier
On Sat, 1 Dec 2001, Anthony Bouvier wrote:
> I have a FUNCTION:
>
> CREATE FUNCTION get_responsible(text)
> RETURNS TEXT AS '
> DECLARE
> responsible_list text;
> my_record RECORD;
> BEGIN
> FOR my_record IN SELECT login FROM employee WHERE id IN (1,2) LOOP
> responsible_list := responsible_list || '', '' my_record.login;
> END LOOP;
> RETURN responsible_list;
> END;
> ' LANGUAGE 'plpgsql';
>
> The employee table is such:
>
> id | login
> -------------
> 1 | anthony
> 2 | mary
> -------------
>
> I expect the SQL statement "SELECT get_responsible('1,2')" to return
> something like so:
>
> get_responsible
> ---------------
> anthony, mary
> ---------------
>
> But instead I receive:
>
> get_responsible
> ---------------
>
> ---------------
You probably need to initialize responsible_list to an empty string.
My guess is that it starts NULL and NULL concatenated with anything
is still NULL.
ok.. here is my deal.. everytime i try to run an insert and my text contains '\' it fails.. an sql example would be. Insert Into table(userID,FirstName,LastName) values(1,'''\''','test') and i get this message... Error while executing the query; ERROR: parser: parse error at or near "test" how can i fix this?? Alex,
On Friday 05 January 2001 05:57, Megalex wrote: > everytime i try to run an insert and my text contains '\' > it fails.. > Insert Into table(userID,FirstName,LastName) values(1,'''\''','test') > and i get this message... > how can i fix this?? by escaping the '\' try Insert Into table(userID,FirstName,LastName) values(1,'\\','test') ant it will work CHeers, HOrst
i already tried escaping it.. but the problem is with the singlequote-backslash-singlequote combinations.. ----- Original Message ----- From: "Horst Herb" <hherb@malleenet.net.au> To: "Megalex" <megalex@klanomega.com> Cc: <pgsql-sql@postgresql.org> Sent: Sunday, December 02, 2001 1:44 AM Subject: Re: [SQL] problems with single quotes.. > On Friday 05 January 2001 05:57, Megalex wrote: > > > everytime i try to run an insert and my text contains '\' > > it fails.. > > > Insert Into table(userID,FirstName,LastName) values(1,'''\''','test') > > and i get this message... > > > how can i fix this?? > > by escaping the '\' > try > Insert Into table(userID,FirstName,LastName) values(1,'\\','test') > ant it will work > > CHeers, > HOrst
On Thu, 4 Jan 2001, Megalex wrote: > ok.. here is my deal.. > > everytime i try to run an insert and my text contains '\' > it fails.. > > an sql example would be. > > Insert Into table(userID,FirstName,LastName) values(1,'''\''','test') > and i get this message... > > Error while executing the query; ERROR: parser: parse error at or near > "test" > > how can i fix this?? You'll need to backslash escape that backslash.
Megalex wrote: > ok.. here is my deal.. > > everytime i try to run an insert and my text contains '\' > it fails.. > > an sql example would be. > > Insert Into table(userID,FirstName,LastName) values(1,'''\''','test') > and i get this message... > > Error while executing the query; ERROR: parser: parse error at or near > "test" > > how can i fix this?? It isn't clear to me what literal you're trying to insert, but it looks like it's either: vsreg_192=# select '\'\'\\\'\''; ?column? ---------- ''\'' (1 row) or vsreg_192=# select '\'\\\''; ?column? ---------- '\' (1 row) HTH, Joe
"Megalex" <megalex@klanomega.com> writes:
> Insert Into table(userID,FirstName,LastName) values(1,'''\''','test')
You're not counting/escaping your quotes correctly. That literal is
unterminated:
' opening quote
'' quoted quote (to put a quote in the string)
\' escaped quote (another way to do the same)
'' quoted quote
oops you're still inside the literal
I don't know what combination of quotes and/or backslashes you actually
meant to insert, but this command is wrong.
regards, tom lane
That was the exact problem. Your help is much appreciated.
Of course, after getting this solution working in respect to the
concatenation, I realise I was going about this whole thing all wrong.
I spent 6 hours trying to do the original with no luck, then came in
today and created an AGGREGATE that does -exactly- what I wanted to do
in the first place, and in a much simpler fashion.
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Stephan Szabo
Sent: Saturday, December 01, 2001 9:23 PM
To: Anthony Bouvier
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Unexpected Return from Function
On Sat, 1 Dec 2001, Anthony Bouvier wrote:
> I have a FUNCTION:
>
> CREATE FUNCTION get_responsible(text)
> RETURNS TEXT AS '
> DECLARE
> responsible_list text;
> my_record RECORD;
> BEGIN
> FOR my_record IN SELECT login FROM employee WHERE id IN (1,2) LOOP
> responsible_list := responsible_list || '', '' my_record.login;
> END LOOP;
> RETURN responsible_list;
> END;
> ' LANGUAGE 'plpgsql';
>
> The employee table is such:
>
> id | login
> -------------
> 1 | anthony
> 2 | mary
> -------------
>
> I expect the SQL statement "SELECT get_responsible('1,2')" to return
> something like so:
>
> get_responsible
> ---------------
> anthony, mary
> ---------------
>
> But instead I receive:
>
> get_responsible
> ---------------
>
> ---------------
You probably need to initialize responsible_list to an empty string.
My guess is that it starts NULL and NULL concatenated with anything
is still NULL.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
> Insert Into table(userID,FirstName,LastName) values(1,'''\''','test') I don't know if you have been able to figure out yet what you need but think of it this way: 'Nick' is a quoted word. let's form the possesive (nick's) 'Nick\'s' notice you still have the outsidequotes. The slash is telling the system to ignore the single quote that directly follows it. so what you want is: INSERT INTO table (userID, FirstName, LastName) VALUES (1, 'william', 'L\'sale') if you intend to have a single quote in the middle of a string (L'sale) Ted -----Original Message----- From: "Megalex" <megalex@klanomega.com> To: <pgsql-sql@postgresql.org> Date: Thu, 4 Jan 2001 12:57:19 -0600 Subject: [SQL] problems with single quotes.. > ok.. here is my deal.. > > everytime i try to run an insert and my text contains '\' > it fails.. > > an sql example would be. > > Insert Into table(userID,FirstName,LastName) values(1,'''\''','test') > and i get this message... > > Error while executing the query; ERROR: parser: parse error at or near > "test" > > how can i fix this?? > > Alex,
On Sun, Dec 02, 2001 at 10:00:22AM -0500, Tom Lane wrote: > > You're not counting/escaping your quotes correctly. That literal is > unterminated: > > ' opening quote > '' quoted quote (to put a quote in the string) > \' escaped quote (another way to do the same) > '' quoted quote > oops you're still inside the literal Section 24.5.1.1. Quote Me on That: Escaping Single Quotes http://www.postgresql.org/idocs/index.php?plpgsql-porting.html This should help. -Roberto -- +----| http://fslc.usu.edu USU Free Software & GNU/Linux Club |------+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer For Sale: Dehydrated H�O - $14 per quart
On Thu, 4 Jan 2001, Megalex wrote: > i already tried escaping it.. but the problem is > with the singlequote-backslash-singlequote combinations.. '''\\''' seems to insert a singlequote-backslash-singlquote for me.
but this is exactally correct behavior.....you can use either two single quotes to escape the quote or the backslash so..... ' '' \\ '' ' singlequote to start the sequence(string) two singlequotes to insert an escape then a singlequote (yields a single quote) a backslash to escape the following backslash (yields a single backslash) two singlequotes to insert an escape then a singlequote (yields a single quote) then ended by a single quote this is acting correctly..... Ted -----Original Message----- From: Stephan Szabo <sszabo@megazone23.bigpanda.com> To: Megalex <megalex@klanomega.com> Date: Sun, 2 Dec 2001 14:21:33 -0800 (PST) Subject: Re: [SQL] problems with single quotes.. > On Thu, 4 Jan 2001, Megalex wrote: > > > i already tried escaping it.. but the problem is > > with the singlequote-backslash-singlequote combinations.. > > '''\\''' seems to insert a singlequote-backslash-singlquote > for me. > > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org >
On Sun, 2 Dec 2001, postgresql wrote: > but this is exactally correct behavior.....you can use either two single quotes to > escape the quote or the backslash so..... > > ' '' \\ '' ' > singlequote to start the sequence(string) > two singlequotes to insert an escape then a singlequote (yields a single quote) > a backslash to escape the following backslash (yields a single backslash) > two singlequotes to insert an escape then a singlequote (yields a single quote) > then ended by a single quote > > this is acting correctly..... Yes. I'm assuming that '\' was what he wanted out, so I gave the sequence that worked for me. He said he'd tried escaping the backslash and didn't have it work, but I'm not sure what he meant by that.
El Thursday 04 January 2001 08:50, Megalex escribió: > i already tried escaping it.. but the problem is > with the singlequote-backslash-singlequote combinations.. I use something like this, and works fine for me: create function quote(text) returns text as ' DECLARE quote text; BEGIN quote := ichar(39); -- quote return quote || $1 || quote; END;' language 'plpgsql'; -- Saludos, lmc@nova.es _________________________________________________________________ I've lost my faith in nihilism