Re: ADO adCmdStoredProc PlPgSql-SP Parameters - Mailing list pgsql-general

From Jeff Eckermann
Subject Re: ADO adCmdStoredProc PlPgSql-SP Parameters
Date
Msg-id cuij3a$23v9$1@news.hub.org
Whole thread Raw
In response to ADO adCmdStoredProc PlPgSql-SP Parameters  (Postgre.News.Firma@spamgourmet.net)
List pgsql-general
<Postgre.News.Firma@spamgourmet.net> wrote in message
news:000301c509f0$ae32c110$ca64a8c0@muenchen.oneXtwo.ag...
> Hi,
>
> How do I call a StoredProcdure, written in PlPqSql
> which returns a set of records (or tableWhatever)
> and wants parameters,
> from ADO while using "adCmdStoredProc" ?

I don't believe that works (anyone who thinks I'm wrong, please correct me).
You will need to use select syntax, e.g. "select * from
setreturningfunction(param1, param2,....);"  If you are using ODBC, you may
have a problem with that syntax, though I believe that the driver code has
been patched to deal with that, and a new release is on its way out (or you
could compile the source).

>
> ERROR:  set-valued function called in context that cannot accept a set
>
>
> ---------------------- VERY LONG VERSION OF THIS QUESTION
> -------------------------
>
>
> When I use Microsoft SQL-Server I make a SP like
>
> CREATE PROCEDURE _test AS
> select * from evt_event
> return 42
> GO
>
> I call it just via name.
>
>
>
>
>
>
> When I use PostgreSql I make a SQL-SP like
>
> CREATE OR REPLACE FUNCTION "public"."_test" () RETURNS SETOF "public"."t1"
> AS
> $body$
> select * from t1;
> $body$
> LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>
> I call it just via name too.
>
>
>
> When I use PostgreSql I make a PLPG-SQL-SP like
> CREATE OR REPLACE FUNCTION "public"."_test" () RETURNS SETOF "public"."t1"
> AS
> $body$
> declare
>       rs record;
> begin
>     for rs in select * from t1 loop
>         return next rs;
>     end loop;
>     RAISE NOTICE '42';
>     return;
> end;
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>
> How do I call this one ? (using adCmdStoredProc)
>
>
> In the SQL-Editor I may call both pg-SPs via "select * from _test()"
>
>
> EMS PostgreSql Manager SQL-Editor:
> VariantePG1: select * from _test() : ok
> VariantePG2: select * from _test() : ok
>
> Ado:
> RECORDSET rs = adoRecordSet.open <command>,...., adCmdStoredProc
>
> VarianteMS : ok
> VariantePG1: ok
> VariantePG2: ERROR:  set-valued function called in context that cannot
> accept a set
>
>
> Help,
> Andreas
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>



pgsql-general by date:

Previous
From: Richard Huxton
Date:
Subject: Re: a few doubts regarding postgres
Next
From: Mark
Date:
Subject: how to capture query?