pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Language SQL, But Need Return PlPgSql
3+ messages / 3 participants
[nested] [flat]
* Re: Language SQL, But Need Return PlPgSql
@ 1970-01-01 00:00 Christoph Haller <ch@rodos.fzk.de>
parent: Yudha Setiawan <inas_husband@BonBon.net>
1 sibling, 0 replies; 3+ messages in thread
From: Christoph Haller @ 1970-01-01 00:00 UTC (permalink / raw)
To: inas_husband@BonBon.net; +Cc: pgsql-sql
Refer to
http://techdocs.postgresql.org/guides/SetReturningFunctions
You'll need 7.3.
Regards, Christoph
^ permalink raw reply [nested|flat] 3+ messages in thread
* Language SQL, But Need Return PlPgSql
@ 2003-04-08 09:52 Yudha Setiawan <inas_husband@BonBon.net>
0 siblings, 2 replies; 3+ messages in thread
From: Yudha Setiawan @ 2003-04-08 09:52 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Language SQL, But Need Return PlPgSql
@ 2003-04-09 11:23 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Yudha Setiawan <inas_husband@BonBon.net>
1 sibling, 0 replies; 3+ messages in thread
From: Rajesh Kumar Mallah @ 2003-04-09 11:23 UTC (permalink / raw)
To: Yudha Setiawan <inas_husband@BonBon.net>; pgsql-sql
Is your problem finally solved?
regds
mallah.
On Tuesday 08 Apr 2003 3:22 pm, Yudha Setiawan wrote:
> 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
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-04-09 11:23 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-08 09:52 Language SQL, But Need Return PlPgSql Yudha Setiawan <inas_husband@BonBon.net>
1970-01-01 00:00 ` Christoph Haller <ch@rodos.fzk.de>
2003-04-09 11:23 ` Rajesh Kumar Mallah <mallah@trade-india.com>
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