Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 632
  • Last Modified:

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;

0
borg48
Asked:
borg48
  • 3
1 Solution
 
earth man2Commented:
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';
0
 
borg48Author Commented:
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
0
 
earth man2Commented:
create view view_x as 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;

your_return_rowset view_x%ROWTYPE;

I suggest you create an OTN account otn.oracle.com an look up the PL/SQL index for ROWTYPE
0
 
earth man2Commented:
Doh Oracle documentation wouldn't be much good for the PostgreSQL forum sorry.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now