Received: from localhost.localdomain (postgresql.org [64.49.215.8])
	by localhost (Postfix) with ESMTP id EE8D0475A09
	for <pgsql-sql@postgresql.org>; Wed,  5 Jun 2002 23:33:37 -0400 (EDT)
Received: from web20409.mail.yahoo.com (web20409.mail.yahoo.com
	[216.136.226.128])
	by postgresql.org (Postfix) with SMTP id 3F4DB475999
	for <pgsql-sql@postgresql.org>; Wed,  5 Jun 2002 23:33:36 -0400 (EDT)
Message-ID: <20020606033341.29202.qmail@web20409.mail.yahoo.com>
Received: from [203.177.60.139] by web20409.mail.yahoo.com via HTTP;
	Wed, 05 Jun 2002 20:33:41 PDT
Date: Wed, 5 Jun 2002 20:33:41 -0700 (PDT)
From: Ludwig Lim <lud_nowhere_man@yahoo.com>
Subject: Re: simple select statement inquiry
To: Joseph Syjuco <joseph@asti.dost.gov.ph>,
	Postgresql Mailing List <pgsql-sql@postgresql.org>
In-Reply-To: <3CFF78C6.82AEDF9A@asti.dost.gov.ph>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Archive-Number: 200206/46
X-Sequence-Number: 8011


--- Joseph Syjuco <joseph@asti.dost.gov.ph> wrote:
> table1
> empno varchar(9) not null
> peer varchar(9) not null references table2(empno)
> superior varchar(9) not null references
> table2(empno)
> 
> table2
> empno varchar(9)
> firstname varchar(20)
> lastname varchar(20)
> 
> what i want to do is get all entries in table 1 and
> transform peer and
> superior fields (which contains empno) into their
> respective firstname +
> lastname in one query
> 
> desired output
> empno             peer             	  superior
> 1000		John Smith		Henry Dunst
> 2000		Juan dela Cruz		Pepe Smith

Create a stored function that will return the fullname
of the "peer" or "superior" given an employee number

CREATE OR REPLACE FUNCTION get_name(VARCHAR(9))
RETURNS VARCHAR(50)
AS'
DECLARE
   v_empno ALIAS FOR $1;
   v_last VARCHAR(20);
   v_first VARCHAR(20);
   v_fullname VARCHAR(50);
BEGIN
   SELECT firstname,lastname
   INTO v_first,v_last
   FROM table2
   WHERE empno=v_empno;
   v_fullname := '''';
   v_fullaname := v_first || '' '' || v_last;
   RETURN v_fullname;
END;'
LANGUAGE 'plpgsql';

then 
type the ff. SELECT stmt:

SELECT empno,get_name(peer),get_name(superior)
FROM table1;

ludwig lim

__________________________________________________
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com