PostgreSQL Discussion - question about to return two diferent tables from a function

Author

question about to return two diferent tables from a function

hildebardo@prodigy.net.mx

2005-09-23, 8:23 pm

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Michael Fuhr

2005-09-24, 3:23 am

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Sponsored Links