Thread: Unexpected Return from Function

Unexpected Return from Function

From
"Anthony Bouvier"
Date:
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



Re: Unexpected Return from Function

From
Stephan Szabo
Date:
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.



problems with single quotes..

From
"Megalex"
Date:
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,









Re: problems with single quotes..

From
Horst Herb
Date:
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


Re: problems with single quotes..

From
"Megalex"
Date:
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

Re: problems with single quotes..

From
Stephan Szabo
Date:
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.



Re: problems with single quotes..

From
Joe Conway
Date:
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



Re: problems with single quotes..

From
Tom Lane
Date:
"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


Re: Unexpected Return from Function

From
"Anthony Bouvier"
Date:
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



Re: problems with single quotes..

From
"postgresql"
Date:
> 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,




Re: problems with single quotes..

From
Roberto Mello
Date:
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


Re: problems with single quotes..

From
Stephan Szabo
Date:
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.



Re: problems with single quotes..

From
"postgresql"
Date:
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
> 




Re: problems with single quotes..

From
Stephan Szabo
Date:
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.



Re: problems with single quotes..

From
Luis Miguel Castañeda
Date:
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