Thread: question about to return two diferent tables from a function
Hi, I have a question. I have two tables first_one doc|date |id_custumer 1 |2005-09-22| 50 2 |2005-09-21| 50 and the second_one doc|date |id_code 12 |2005-09-01| 20 13 |2005-09-21| 24 14 |2005-09-22| 31 then I need to join the two tables in a function like this doc|date |id 1 |2005-09-22| 50 2 |2005-09-21| 50 12 |2005-09-01| 20 13 |2005-09-21| 24 14 |2005-09-22| 31 with just one table I don't have a problem, CREATE OR REPLACE FUNCTION test_function() RETURNS setof record AS 'select * from first_one' LANGUAGE 'sql'; CH=> select * from test_function() as result(doc int8, date date, id int8); doc | date | id -----+------------+---- 12 | 2005-09-01 | 20 13 | 2005-09-21 | 24 14 | 2005-09-22 | 31 but I don't know how call the second table. any help? Thanks a lot Luis Hildebardo Moreno hildebardo@prodigy.net.mx San Luis Potosi, Mexico
On Fri, Sep 23, 2005 at 07:55:32PM -0500, hildebardo@prodigy.net.mx wrote: > I have two tables [...] > then I need to join the two tables in a function like this > > doc|date |id > 1 |2005-09-22| 50 > 2 |2005-09-21| 50 > 12 |2005-09-01| 20 > 13 |2005-09-21| 24 > 14 |2005-09-22| 31 This doesn't look like a join of the two tables; it looks instead like a union. Is this the query you're looking for? SELECT doc, date, id_customer AS id FROM first_one UNION ALL SELECT doc, date, id_code AS id FROM second_one; -- Michael Fuhr