Thread: Problem on function returning setof custom type

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, test.blow, test1.bla2
   FROM test
   LEFT JOIN test1 ON test.id = 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, test.blow, test1.bla2
   FROM test
   LEFT JOIN test1 ON test.id = 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!

On Wed, 22 Jun 2005, Pablo Baena wrote:

> 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

This is going to compare to the exact string '$1' not to the value of the
argument since you've quoted it.  I think you'd just want test1.bla2 = $1
there.

You are amazing! It solved it! Thanks!!




--
    > There are a lot of us out there who both do and do not work for Sun

    Wow! Quantum programmers!