From: | Pablo Baena <pbaena(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Problem on function returning setof custom type |
Date: | 2005-06-22 15:36:32 |
Message-ID: | 36be2c7a050622083648a34cc8@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
This happens when using left join on the select.
This is a sample of what is happening to me.
-- The tables
CREATE TABLE test
(
id numeric,
blow varchar
) WITHOUT OIDS;
CREATE TABLE test1
(
id numeric,
bla2 varchar
) WITHOUT OIDS;
COPY test (id, blow) FROM stdin;
1 sdasd
2 sdaddxxsd
4 s55ff
\.
COPY test1 (id, bla2) FROM stdin;
1 dddd
\.
--the custom type:
CREATE TABLE test1
(
id numeric,
bla2 varchar
) WITHOUT OIDS;
-- the function:
CREATE OR REPLACE FUNCTION vv(varchar)
RETURNS SETOF custom AS
'
DECLARE
rec custom%ROWTYPE;
BEGIN
FOR rec IN SELECT test.id <http://test.id>, test.blow, test1.bla2
FROM test
LEFT JOIN test1 ON test.id <http://test.id> = test1.id <http://test1.id>WHERE
test1.bla2=\'$1\' LOOP
RETURN NEXT rec;
END LOOP;
RETURN null;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
-- the results:
select * from vv ('dddd');
-- returns empty set
-- while the actual sql query:
SELECT test.id <http://test.id>, test.blow, test1.bla2
FROM test
LEFT JOIN test1 ON test.id <http://test.id> = test1.id <http://test1.id>WHERE
test1.bla2='dddd';
-- returns
--id|blow|bla2
--1|sdasd|dddd
Thanks for the help!
--
> There are a lot of us out there who both do and do not work for Sun
Wow! Quantum programmers!
From | Date | Subject | |
---|---|---|---|
Next Message | Sean Cardus | 2005-06-22 15:50:36 | Re: Problems upgrading to 7.4.8 from 7.2.4 |
Previous Message | marcelo Cortez | 2005-06-22 15:23:37 | how to xml on debian? |