Problem on function returning setof custom type

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!

Responses

Browse pgsql-general by date

  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?