From: Ludwig Lim <lud_nowhere_man@yahoo.com>
To: Joseph Syjuco <joseph@asti.dost.gov.ph>
To: Postgresql Mailing List <pgsql-sql@postgresql.org>
Subject: Re: simple select statement inquiry
Date: Wed, 5 Jun 2002 20:33:41 -0700 (PDT)
Message-ID: <20020606033341.29202.qmail@web20409.mail.yahoo.com> (raw)
In-Reply-To: <3CFF78C6.82AEDF9A@asti.dost.gov.ph>
--- 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lud_nowhere_man@yahoo.com, joseph@asti.dost.gov.ph
Subject: Re: simple select statement inquiry
In-Reply-To: <20020606033341.29202.qmail@web20409.mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox