Problem on function returning setof custom type
От | Pablo Baena |
---|---|
Тема | Problem on function returning setof custom type |
Дата | |
Msg-id | 36be2c7a050622083648a34cc8@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Problem on function returning setof custom type
|
Список | 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;
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!
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;--the custom type:
1 dddd
\.
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!
В списке pgsql-general по дате отправления: