asked on

stored procedure return multiple rows from tables

I'm trying to figure this one out. New to postgresql. But i have a query that i would like to return all the rows it finds from multiple tables.

Ex.
select a.fname, a.mi, a.lastname, b.address, b.city, c.ssn, c.bday
from
name as a,
address as b,
misc as c

i have read the setof but the sample i see uses the table name. Since i have multiple tables how do you do it in a stored procedure:

This is the sample i saw.
create function getdata() returns setof foo as $$
select * from foo where;
$$ language sql;

earth man2

here's one I prepared earlier

create function periods_logged_on() returns setof login_period as $$
declare
cp tbllog%ROWTYPE;
pd login_period%ROWTYPE;
begin
for cp in select * from tbllog order by tlUsername, tlTimestamp, tlEntryType l
oop
if ( cp.tlEntryType = 'LOGIN' ) then
pd.tlusername := cp.tlusername;
pd.logintime := cp.tltimestamp;
elsif ( cp.tlEntryType = 'LOGOUT' ) then
if ( pd.tlusername = cp.tlusername ) then
pd.logouttime := cp.tltimestamp;
return next pd;
end if;
end if;
end loop;
return;
end;
$$ language 'plpgsql';

borg48

ASKER

I'm kind of new to this so its not that clear to be what for example:
cp tbllog%ROWTYPE;
pd login_period%ROWTYPE;
means

How would you do it with my sample tables:
select a.fname, a.mi, a.lastname, b.address, b.city, c.ssn, c.bday
from
name as a,
address as b,
misc as c

Thanks

ASKER CERTIFIED SOLUTION

earth man2

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

earth man2

Doh Oracle documentation wouldn't be much good for the PostgreSQL forum sorry.