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> 3 5287
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>
"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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Rowland Hills |
last post by:
I have a table which is returning inconsistent results when I query
it!
In query analyzer:
If I do "SELECT * FROM TABLE_NAME" I get no rows returned.
If I do "SELECT COL1, COL2 FROM...
|
by: malcolm |
last post by:
Example, suppose you have these 2 tables
(NOTE: My example is totally different, but I'm simply trying to setup
the a simpler version, so excuse the bad design; not the point here)
CarsSold {...
|
by: grunar |
last post by:
After some thought on what I need in a Python ORM (multiple primary
keys, complex joins, case statements etc.), and after having built
these libraries for other un-named languages, I decided to...
|
by: joseph speigle |
last post by:
hello list,
I want to do something like the following:
address=# @var = select max(id) from passwd;
ERROR: parser: parse error at or near "@" at character 1
address=# var = select max(id)...
|
by: Tcs |
last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't
loaded it yet. I'm still using MS Access. And no, I don't believe this is an
Access question. (But who knows? I...
|
by: William Wisnieski |
last post by:
Hello Everyone,
I have a main form with a datasheet subform that I use to query by form.
After the user selects two criteria on the main form and clicks the
cmdShowResults button on the main...
|
by: Andy Kriger |
last post by:
I would like to select strings from a table and return them as an array
For example,
select new_array(name) from my_tbl
would return
String { name1, name2, name3, etc }
Is this possible with...
|
by: Jimmy |
last post by:
thanks to everyone that helped, unfortunately the code samples people gave me don't work. here is what i have so far:
<%
Dim oConn, oRS, randNum
Randomize()
randNum = (CInt(1000 * Rnd) + 1) *...
|
by: MP |
last post by:
vb6,ado,mdb,win2k
i pass the sql string to the .Execute method on the open connection to
Table_Name(const) db table
fwiw
(the connection opened via class wrapper:)
msConnString = "Data Source="...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |