471,108 Members | 1,307 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,108 software developers and data experts.

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

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_pa th($1)))
ORDER BY client_key, dsrc_id DESC;
' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT;
</code>

Nov 12 '05 #1
3 5173
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***************@prajnait.com> wrote in message
news:RP********************@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_pa th($1)))
ORDER BY client_key, dsrc_id DESC;
' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT;
</code>

Nov 12 '05 #2
"ezra epstein" <ee***************@prajnait.com> writes:
IF NOT EXISTS(SELECT 1 FROM merchandise.ds_item($1) WHERE "client_key" =
$2) THEN
[ works, but this doesn't: ]
SELECT INTO rec * FROM merchandise.ds_item($1) WHERE "client_key" = %2;


If that's an accurate transcription, then your error is using %2 where
you meant $2.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #3
Ahhh. Yes. A nasty typo. I did a global search for %1, %2, %3, etc. and
turned up several elsewhere. Ouch. Thank you for your close reading.

== Ezra Epstein

"Tom Lane" <tg*@sss.pgh.pa.us> wrote in message
news:20**************@sss.pgh.pa.us...
"ezra epstein" <ee***************@prajnait.com> writes:
IF NOT EXISTS(SELECT 1 FROM merchandise.ds_item($1) WHERE "client_key" = $2) THEN
[ works, but this doesn't: ]
SELECT INTO rec * FROM merchandise.ds_item($1) WHERE "client_key" =
%2;
If that's an accurate transcription, then your error is using %2 where
you meant $2.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Tcs | last post: by
8 posts views Thread by Andy Kriger | last post: by
22 posts views Thread by MP | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.