Language SQL, But Need Return PlPgSql

From: "Yudha Setiawan" <inas_husband(at)BonBon(dot)net>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Language SQL, But Need Return PlPgSql
Date: 2003-04-08 09:52:49
Message-ID: 018101c2fdb8$6e46d1e0$ea00a8c0@yudha
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Dear Expert;
Gimme your hand please,
I've been using function with "Language SQL" and combined with
"type" for the simply-one. Like this;
-------------------------------------
create type simply_1 as(
fc_name char(20),
fn_codedept integer,
fc_descdept char(30) );

Create function F_Simple(integer) returns setof d_4developer.ty_rep_coba as
' select a.fc_name, a.fn_codedept, b.fc_descdept
from Emplo_Corp a, Depart b
where a.fn_codedept = b.fn_codedept and
a.fn_codedept = $1;
' language SQL;
--------------------------------------

That's running pretty good. But now i have more complicated condition.
There are hundreds combination of reports must be handled by me and
most of all have the same result. If i still using style of "Language
SQL", that's means I will have to make function as much as my report.
I Tried Using style "Language PlPgSql" but I got a Fully Confused
about what a kind of RETURN i will use ??
-------------------------------------------------
Create or replace function MultiRpt(integer,char(1))
setof d_4developer.ty_rep_coba as
'begin
-- View Corporate's Employee
If $2 = ''C'' Then
select a.fc_name, a.fn_codedept, b.fc_descdept
from Emplo_Corp a, depart b
where a.fn_codedept = b.fn_codedept and
a.fn_codedept = $1;
End If;

-- View Branch'A' Employee
If $2 = ''A'' Then
select a.fc_name, a.fn_codedept, b.fc_descdept
from Emplo_BRANCH_A a, depart b
where a.fn_codedept = b.fn_codedept and
a.fn_codedept = $1;
End If;

-- View Branch'B' Employee
If $2 = ''B'' Then
select a.fc_name, a.fn_codedept, b.fc_descdept
from Emplo_BRANCH_B a, depart b
where a.fn_codedept = b.fn_codedept and
a.fn_codedept = $1;
End If;'
Return (-- ?? --)
end;' language PlPgSql;
-------------------------------------------------

Some advice will be helpfull. Thank's
GOD Bless You All and Bravo Postgres !!

Yudha Setiawan.
VoIP
No. : 628899911223344

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message sirius 2003-04-08 09:54:05 create databases with plpgsql
Previous Message Victor Yegorov 2003-04-08 09:40:39 Re: CASE