Hi,
I don't know if this is a bug, but at least I haven't found any clear
statement in documentation about; this should be wrote with big and bold
letters.
In any way I think this is bug or big inconsistency, because of, as was
stated in previous mail
test=# CREATE FUNCTION p_enhance_address3 (address OUT u_address_type,
i1 OUT
int)
AS $$
BEGIN
SELECT t_author.address
INTO address
FROM t_author
WHERE first_name = 'George';
i1 = 12;
END;
$$ LANGUAGE plpgsql;
test=# select *
from p_enhance_address3();
address | i1
----------------------------------------------------+----
("(""(""""Parliament Hill"""",77)"",NW31A9)",,,,,) | 12
(1 row),
but if you will create above function without last, i1 parameter
(SELECT * FROM p_enhance_address2();) then result will be
street | zip | city | country | since |
code
-------------------------------------+-----+------+---------+-------+------
("(""Parliament Hill"",77)",NW31A9) | | | | |
In last case, I think, result should be "packed" in one column, because
of it clearly "unpacked" record.
On Tue, 25 Jan 2011 14:39:51 +0700, Lukas Eder wrote:
Here, we've somehow got the first two fields of u_address_type -
street and
zip - squashed together into one column named 'street', and all
the other
columns nulled out.
I think this is the old problem of PL/pgsql having two forms of
SELECT
INTO. You can either say:
SELECT col1, col2, col3, ... INTO recordvar FROM ...
Or you can say:
SELECT col1, col2, col3, ... INTO nonrecordvar1, nonrecordvar2,
nonrecordvar3, ... FROM ...
In this case, since address is a recordvar, it's expecting the first
form - thus the first select-list item gets matched to the first
column of the address, rather than to address as a whole. It's not
smart enough to consider the types of the items involved - only
whether they are records. :-(
So what you're suggesting is that the plpgsql code is causing the
issues? Are there any indications about how I could re-write this
code? The important thing for me is to have the aforementioned
signature of the plpgsql function with one UDT OUT parameter. Even
if this is a bit awkward in general, in this case, I don't mind
rewriting the plpgsql function content to create a workaround for
this problem...
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
