SELECT INTO broken (?) in PL/pgSQL when using a set returning function in FROM clause (BGUG?)
От | ezra epstein |
---|---|
Тема | SELECT INTO broken (?) in PL/pgSQL when using a set returning function in FROM clause (BGUG?) |
Дата | |
Msg-id | RPCcnc_G2cQVbXWiXTWc-g@speakeasy.net обсуждение исходный текст |
Ответы |
Re: SELECT INTO broken (?) in PL/pgSQL when using a set returning function in FROM clause (BGUG?)
|
Список | pgsql-general |
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>
В списке pgsql-general по дате отправления: