pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRETURNS SETOF primitive returns results in parentheses
8+ messages / 4 participants
[nested] [flat]
* RETURNS SETOF primitive returns results in parentheses
@ 2005-10-26 12:38 Mario Splivalo <mario.splivalo@mobart.hr>
2005-10-26 12:54 ` Re: RETURNS SETOF primitive returns results in parentheses Sean Davis <sdavis2@mail.nih.gov>
2005-10-26 14:40 ` Re: RETURNS SETOF primitive returns results in parentheses Tom Lane <tgl@sss.pgh.pa.us>
2005-10-26 14:46 ` Re: RETURNS SETOF primitive returns results in parentheses Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 3 replies; 8+ messages in thread
From: Mario Splivalo @ 2005-10-26 12:38 UTC (permalink / raw)
To: pgsql-sql
Consider this function:
CREATE OR REPLACE FUNCTION php_get_subfield_data_repeating(int4,
"varchar")
RETURNS SETOF "varchar" AS
$BODY$
DECLARE
aRecordID ALIAS FOR $1;
aSubFieldId ALIAS FOR $2;
returnValue record;
subFieldNumber char(3);
subFieldLetter char(1);
BEGIN
subFieldNumber = substr(aSubFieldId, 1, 3);
subFieldLetter = substr(aSubFieldId, 4);
FOR returnValue IN SELECT "subfieldValue"::varchar
FROM "records_sub"
WHERE "fieldTag" = subFieldNumber AND "subfieldTag" = subFieldLetter
AND "recordId" = aRecordId
LOOP
RETURN NEXT returnValue;
END LOOP;
RETURN;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
Now, when I do this:
biblio3=# select * from php_get_subfield_data_repeating(1,'606a');
php_get_subfield_data_repeating1
----------------------------------
(Anđeli)
(ofsajd)
(2 rows)
I have return values in parentheses. However, if I create a new type:
CREATE TYPE subfield_data_type AS (subfield_data varchar);
And then drop the function and recreate it like this:
CREATE OR REPLACE FUNCTION php_get_subfield_data_repeating(int4,
"varchar")
RETURNS SETOF subfield_data_type AS
$BODY$
...
And then when I run the function, the results are ok:
biblio3=# select * from php_get_subfield_data_repeating(1,'606a');
subfield_data
---------------
Anđeli
ofsajd
(2 rows)
Am I doing something wrong here? Why do I need to create type with only
one member of type varchar to have results without the parentheses?
Mike
P.S. The subFieldValue field in the records_sub table is of type
varchar(4096).
--
Mario Splivalo
Mob-Art
mario.splivalo@mobart.hr
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: RETURNS SETOF primitive returns results in parentheses
2005-10-26 12:38 RETURNS SETOF primitive returns results in parentheses Mario Splivalo <mario.splivalo@mobart.hr>
@ 2005-10-26 12:54 ` Sean Davis <sdavis2@mail.nih.gov>
2005-10-26 13:09 ` Re: RETURNS SETOF primitive returns results in parentheses Mario Splivalo <mario.splivalo@mobart.hr>
2 siblings, 1 reply; 8+ messages in thread
From: Sean Davis @ 2005-10-26 12:54 UTC (permalink / raw)
To: mario.splivalo@mobart.hr; pgsql-sql
On 10/26/05 8:38 AM, "Mario Splivalo" <mario.splivalo@mobart.hr> wrote:
> Consider this function:
>
> CREATE OR REPLACE FUNCTION php_get_subfield_data_repeating(int4,
> "varchar")
> RETURNS SETOF "varchar" AS
> $BODY$
> DECLARE
> aRecordID ALIAS FOR $1;
> aSubFieldId ALIAS FOR $2;
>
> returnValue record;
> subFieldNumber char(3);
> subFieldLetter char(1);
>
> BEGIN
> subFieldNumber = substr(aSubFieldId, 1, 3);
> subFieldLetter = substr(aSubFieldId, 4);
>
> FOR returnValue IN SELECT "subfieldValue"::varchar
> FROM "records_sub"
> WHERE "fieldTag" = subFieldNumber AND "subfieldTag" = subFieldLetter
> AND "recordId" = aRecordId
> LOOP
> RETURN NEXT returnValue;
> END LOOP;
>
> RETURN;
> END
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
>
>
>
> Now, when I do this:
>
> biblio3=# select * from php_get_subfield_data_repeating(1,'606a');
> php_get_subfield_data_repeating1
> ----------------------------------
> (An©¢eli)
> (ofsajd)
> (2 rows)
Does:
select * from php_get_subfield_data_repeating(1,'606a') as s(a)
do what you want (single column)?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: RETURNS SETOF primitive returns results in parentheses
2005-10-26 12:38 RETURNS SETOF primitive returns results in parentheses Mario Splivalo <mario.splivalo@mobart.hr>
2005-10-26 12:54 ` Re: RETURNS SETOF primitive returns results in parentheses Sean Davis <sdavis2@mail.nih.gov>
@ 2005-10-26 13:09 ` Mario Splivalo <mario.splivalo@mobart.hr>
0 siblings, 0 replies; 8+ messages in thread
From: Mario Splivalo @ 2005-10-26 13:09 UTC (permalink / raw)
To: pgsql-sql
On Wed, 2005-10-26 at 08:54 -0400, Sean Davis wrote:
> > Now, when I do this:
> >
> > biblio3=# select * from php_get_subfield_data_repeating(1,'606a');
> > php_get_subfield_data_repeating1
> > ----------------------------------
> > (Anđeli)
> > (ofsajd)
> > (2 rows)
>
> Does:
>
> select * from php_get_subfield_data_repeating(1,'606a') as s(a)
>
> do what you want (single column)?
>
Nope. I still get the results in parentheses. When I change the SETOF
from varchar to my defined type, your query [with as s(a)] I get normal
resutls, withouth parentheses. I clearly have solved a problem, I just
need to create a type containing one member only, with the type of
varchar, and instead of 'RETURNS SETOF varchar' i need to do 'RETURNS
SETOF my_varchar_type'. I'm just wondering is this like that 'by
design', or is it a bug.
I've been reproducing this on postgres versions 7.4.8, 7.4.9, 8.0.3,
8.0.4 and 8.1beta3.
Mike
--
Mario Splivalo
Mob-Art
mario.splivalo@mobart.hr
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: RETURNS SETOF primitive returns results in parentheses
2005-10-26 12:38 RETURNS SETOF primitive returns results in parentheses Mario Splivalo <mario.splivalo@mobart.hr>
@ 2005-10-26 14:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-26 19:41 ` Re: RETURNS SETOF primitive returns results in parentheses Mario Splivalo <mario.splivalo@mobart.hr>
2 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2005-10-26 14:40 UTC (permalink / raw)
To: mario.splivalo@mobart.hr; +Cc: pgsql-sql
Mario Splivalo <mario.splivalo@mobart.hr> writes:
> biblio3=# select * from php_get_subfield_data_repeating(1,'606a');
> php_get_subfield_data_repeating1
> ----------------------------------
> (Anđeli)
> (ofsajd)
> (2 rows)
> I have return values in parentheses.
You're getting bit by plpgsql's perhaps-excessive willingness to convert
datatypes. Your returnValue variable is not a varchar, it is a record
that happens to contain one varchar field. When you do "RETURN NEXT
returnValue", plpgsql has to coerce that record value to varchar, and
it does that by converting the record value to text ... which produces
the parenthesized data format specified at
http://www.postgresql.org/docs/8.0/static/rowtypes.html#AEN5604
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: RETURNS SETOF primitive returns results in parentheses
2005-10-26 12:38 RETURNS SETOF primitive returns results in parentheses Mario Splivalo <mario.splivalo@mobart.hr>
2005-10-26 14:40 ` Re: RETURNS SETOF primitive returns results in parentheses Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-10-26 19:41 ` Mario Splivalo <mario.splivalo@mobart.hr>
2005-10-26 19:45 ` Re: RETURNS SETOF primitive returns results in parentheses Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Mario Splivalo @ 2005-10-26 19:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On Wed, 2005-10-26 at 10:40 -0400, Tom Lane wrote:
> Mario Splivalo <mario.splivalo@mobart.hr> writes:
> > biblio3=# select * from php_get_subfield_data_repeating(1,'606a');
> > php_get_subfield_data_repeating1
> > ----------------------------------
> > (Anđeli)
> > (ofsajd)
> > (2 rows)
>
> > I have return values in parentheses.
>
> You're getting bit by plpgsql's perhaps-excessive willingness to convert
> datatypes. Your returnValue variable is not a varchar, it is a record
> that happens to contain one varchar field. When you do "RETURN NEXT
> returnValue", plpgsql has to coerce that record value to varchar, and
> it does that by converting the record value to text ... which produces
> the parenthesized data format specified at
> http://www.postgresql.org/docs/8.0/static/rowtypes.html#AEN5604
Wo-ha, makes perfect sense. So, I'd go by with declaring the rec as
varchar, instead as of a record. Wich is what I should do in the first
place.
Thnx for pointing that out. :)
Mike
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: RETURNS SETOF primitive returns results in parentheses
2005-10-26 12:38 RETURNS SETOF primitive returns results in parentheses Mario Splivalo <mario.splivalo@mobart.hr>
2005-10-26 14:40 ` Re: RETURNS SETOF primitive returns results in parentheses Tom Lane <tgl@sss.pgh.pa.us>
2005-10-26 19:41 ` Re: RETURNS SETOF primitive returns results in parentheses Mario Splivalo <mario.splivalo@mobart.hr>
@ 2005-10-26 19:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-26 19:57 ` Re: RETURNS SETOF primitive returns results in parentheses Mario Splivalo <mario.splivalo@mobart.hr>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2005-10-26 19:45 UTC (permalink / raw)
To: mario.splivalo@mobart.hr; +Cc: pgsql-sql
Mario Splivalo <mario.splivalo@mobart.hr> writes:
> Wo-ha, makes perfect sense. So, I'd go by with declaring the rec as
> varchar, instead as of a record. Wich is what I should do in the first
> place.
Or just return the correct field out of it.
RETURN NEXT returnValue.fieldname;
I think you may have to do it this way because the FOR loop wants a
record variable.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: RETURNS SETOF primitive returns results in parentheses
2005-10-26 12:38 RETURNS SETOF primitive returns results in parentheses Mario Splivalo <mario.splivalo@mobart.hr>
2005-10-26 14:40 ` Re: RETURNS SETOF primitive returns results in parentheses Tom Lane <tgl@sss.pgh.pa.us>
2005-10-26 19:41 ` Re: RETURNS SETOF primitive returns results in parentheses Mario Splivalo <mario.splivalo@mobart.hr>
2005-10-26 19:45 ` Re: RETURNS SETOF primitive returns results in parentheses Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-10-26 19:57 ` Mario Splivalo <mario.splivalo@mobart.hr>
0 siblings, 0 replies; 8+ messages in thread
From: Mario Splivalo @ 2005-10-26 19:57 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
On Wed, 2005-10-26 at 15:45 -0400, Tom Lane wrote:
> Mario Splivalo <mario.splivalo@mobart.hr> writes:
> > Wo-ha, makes perfect sense. So, I'd go by with declaring the rec as
> > varchar, instead as of a record. Wich is what I should do in the first
> > place.
>
> Or just return the correct field out of it.
>
> RETURN NEXT returnValue.fieldname;
>
> I think you may have to do it this way because the FOR loop wants a
> record variable.
Yes, you're right. Funny implementation of the FOR loop :)
Mike
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: RETURNS SETOF primitive returns results in parentheses
2005-10-26 12:38 RETURNS SETOF primitive returns results in parentheses Mario Splivalo <mario.splivalo@mobart.hr>
@ 2005-10-26 14:46 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2 siblings, 0 replies; 8+ messages in thread
From: Stephan Szabo @ 2005-10-26 14:46 UTC (permalink / raw)
To: Mario Splivalo <mario.splivalo@mobart.hr>; +Cc: pgsql-sql
On Wed, 26 Oct 2005, Mario Splivalo wrote:
> Consider this function:
>
> CREATE OR REPLACE FUNCTION php_get_subfield_data_repeating(int4,
> "varchar")
> RETURNS SETOF "varchar" AS
> $BODY$
> DECLARE
> aRecordID ALIAS FOR $1;
> aSubFieldId ALIAS FOR $2;
>
> returnValue record;
> subFieldNumber char(3);
> subFieldLetter char(1);
>
> BEGIN
> subFieldNumber = substr(aSubFieldId, 1, 3);
> subFieldLetter = substr(aSubFieldId, 4);
>
> FOR returnValue IN SELECT "subfieldValue"::varchar
> FROM "records_sub"
> WHERE "fieldTag" = subFieldNumber AND "subfieldTag" = subFieldLetter
> AND "recordId" = aRecordId
> LOOP
> RETURN NEXT returnValue;
I think the root cause is that you're not returning a varchar here, but
instead a record containing a varchar (if I return next
returnValue."subfieldValue" I don't seem to get parens). I'm not sure why
it's allowing you to do so, though, it seems like that shouldn't match the
return type.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2005-10-26 19:57 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-26 12:38 RETURNS SETOF primitive returns results in parentheses Mario Splivalo <mario.splivalo@mobart.hr>
2005-10-26 12:54 ` Sean Davis <sdavis2@mail.nih.gov>
2005-10-26 13:09 ` Mario Splivalo <mario.splivalo@mobart.hr>
2005-10-26 14:40 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-26 19:41 ` Mario Splivalo <mario.splivalo@mobart.hr>
2005-10-26 19:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-10-26 19:57 ` Mario Splivalo <mario.splivalo@mobart.hr>
2005-10-26 14:46 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox