Lists: | pgsql-general |
---|
From: | "LLC" <kevin(at)kevinkempterllc(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | (no subject) |
Date: | 2006-06-16 18:49:44 |
Message-ID: | 1150483784.v2.fusewebmail-164106@f |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Hi List;
I've created a plperlu (perl untrusted) function with the ability to use
dbi. I can use the code listing below to return data if I specify the
query as follows:
select * from sybase_get2() as (f1 varchar(100), f2 varchar(100));
However, I have to specify the column names as f1 and f2.
I want to turn this into a generic solution where I can eventually pass in
the SQL. For now I just want to be able to specify any column names I want
in the "as" part of the above query.
Any Ideas?
Thanks in advance for your help...
########### Code Listing Start ###########
CREATE OR REPLACE FUNCTION sybase_get2()
RETURNS SETOF record
AS $$
use DBI;
my $dbh = DBI->connect("dbi:Sybase:server=CXS100", 'pguser1', 'pg70093' );
if (!$dbh) {
return;
}
my $qry = $dbh->prepare("select parent_id, Parent_key from
csx1db.dbo.parentkeys");
$qry->execute();
while ( @data = $qry->fetchrow_array() ) {
return_next ({f1=>$data[0], f2=>$data[1]});
}
return ;
$$ LANGUAGE plperlu
########### Code Listing End ###########
From: | Tony Caduto <tony_caduto(at)amsoftwaredesign(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: (no subject) |
Date: | 2006-06-16 20:33:26 |
Message-ID: | 44931596.7060706@amsoftwaredesign.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
LLC wrote:
> Hi List;
>
> I've created a plperlu (perl untrusted) function with the ability to use
> dbi. I can use the code listing below to return data if I specify the
> query as follows:
>
> select * from sybase_get2() as (f1 varchar(100), f2 varchar(100));
>
> However, I have to specify the column names as f1 and f2.
> I want to turn this into a generic solution where I can eventually pass in
> the SQL. For now I just want to be able to specify any column names I want
> in the "as" part of the above query.
>
> Any Ideas?
>
>
Use a type to define the output columns, then you can call the function
like this:
select * from sybase_get2()
not need to us AS
See the examples on this page on how to use a type with a plperl function:
http://www.commandprompt.com/community/pgdocs81/plperl-database
--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration