Re: SELECT INTO broken (?) in PL/pgSQL when using a set returning function in FROM clause (BGUG?)

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>
>
>
>