return type of PostgreSQL function when joining 2 tables

Posted on 2005-04-17
Medium Priority
Last Modified: 2008-03-04

I'm trying to create a new postgresql function (stored procedure) and I would like to know which return type I've to use in the following query :

select *
from table1 t1,table2 t2
where t1.pid = t2.pid

I cannot use the return type table1 because the query also returns table2-values.

I've already tried this : RETURNS SETOF "anyelement". But it doesn't work.

Thanks ...

Davy (Belgium)
Question by:davyberroho
LVL 22

Accepted Solution

earth man2 earned 2000 total points
ID: 13800751
create view UR_VIEW as
select *
from table1 t1,table2 t2
where t1.pid = t2.pid

create function x returns set of UR_VIEW

Expert Comment

ID: 13805411
You can also declare function to return 'setof record' type, but his has one disadvantage: when you do SELECT from this function, you have to write all fields and their types in the statement like this:

SELECT * from my_function(argument) (int field1, text field2, timestamp filed3....) (just an example, look in the manual for syntax and usage info).


Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month17 days, 4 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question