Up too late. This works fine. At least it works for some simpler test
functions under the just-downloaded 7.4.1 release.
-- Ezra Epstein
"ezra epstein" <ee_newsgroup_post@prajnait.com> wrote in message
news:RPCcnc_G2cQVbXWiXTWc-g@speakeasy.net...
> I'm been banging my head over this for a little while now.
>
> Here's a simple function to return a record:
>
> <code>
> CREATE OR REPLACE FUNCTION create_item_record_for_override(INTEGER,
> VARCHAR )
> RETURNS RECORD AS '
> DECLARE
> rec RECORD;
> BEGIN
> /* Normally we would not have a separate check here. We would use IF
> NOT FOUND, but that appears to be broken. */
> IF NOT EXISTS(SELECT 1 FROM merchandise.ds_item($1) WHERE "client_key"
=
> $2) THEN
> RAISE EXCEPTION ''No base row for override. dsrc_id=%,
> client_key=%"'', $1, $2;
> END IF;
>
> SELECT INTO rec * FROM merchandise.ds_item($1) WHERE "client_key" =
%2;
> IF NOT FOUND THEN
> /* We should NEVER get here. The EXISTS check uses the same query
> and so will raise an exception under the same conditions. IT APPEARS as
> though SELECT INTO is not working when there is a function in the FROM
> clause. */
> RAISE EXCEPTION ''No base row for override. dsrc_id=%,
> client_key=%"'', $1, $2;
> END IF;
>
> RETURN rec;
> END;
> ' LANGUAGE plpgsql STABLE;
> </code>
>
> Basically passing in valid parameters, one's where the result of doing
> SELECT * FROM merchandise.ds_item($1) WHERE "client_key" = %2;
> on the psql command line work just fine, fail always in this function.
All
> I can conclude (after about 2 hours) is that we can not SELECT INTO, FROM
a
> set returning function in PL/pgSQL.
>
> For the curious, here is the definition of the ds_item function.
>
> <code>
> CREATE OR REPLACE FUNCTION "merchandise".ds_item(INTEGER)
> RETURNS setof "merchandise"."item" AS '
> SELECT DISTINCT ON (client_key) * FROM "merchandise"."item"
> WHERE "ovr_status" >= 0 AND "dsrc_id" IN (SELECT * FROM
> "common".get_path_parts("merchandise".ds_get_id_path($1)))
> ORDER BY client_key, dsrc_id DESC;
> ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT;
> </code>
>
>
>
