[SQL] Error calling self-made plpgsql function "function XYZ(bigint) does not exist"
Hi
I have problem calling my function (prety easy):
== sql begin ==
CREATE OR REPLACE FUNCTION "InventGroups_GetAllParents"(int8)
RETURNS SETOF "ItemGroupRelationNode" AS
$BODY$declare
R RECORD;
SR RECORD;
begin
FOR R IN select ItemGroupId, ParentItemGroupId
from InventGroups where ItemGroupId = $1 and ItemGroupId > 0
LOOP
RETURN NEXT R;
--FOR SR IN select * from InventGroups_GetAllParents(
R.ParentItemGroupId::int8 )
--LOOP
--RETURN NEXT SR;
--END LOOP;
END LOOP;
RETURN;
end;$BODY$
LANGUAGE 'plpgsql' VOLATILE;
== sql end ==
"ItemGroupRelationNode" is a complex type
CREATE TYPE "ItemGroupRelationNode" AS
("ItemGroupId" int8,
"ParentItemGroupId" int8);
The result is:
=
select * from InventGroups_GetAllParents(0::int8)
===
ERROR: function inventgroups_getallparents(bigint) does not exist
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.
Thanks for responce
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Error calling self-made plpgsql function "function XYZ(bigint) does not exist"
On Jan 19, 2006, at 21:39 , Juris wrote: == sql begin == CREATE OR REPLACE FUNCTION "InventGroups_GetAllParents"(int8) select * from InventGroups_GetAllParents(0::int8) === ERROR: function inventgroups_getallparents(bigint) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. If you double-quote your function name (or any identifier) when you create it, you'll need to double-quote them when you call the function as well. Try: select * from "InventGroups_GetAllParents"(0::int8) Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Error calling self-made plpgsql function "function XYZ(bigint) does not exist"
Argh... big thanks.. did not know what pgAdmin/PG have any case-sensitive issues with functions... Also for fields it is relative... without dbl-quotes i could not query anything (i am using "MySuperField"-like field names) 2006/1/19, Michael Glaesemann <[EMAIL PROTECTED]>: > > On Jan 19, 2006, at 21:39 , Juris wrote: > > > == sql begin == > > CREATE OR REPLACE FUNCTION "InventGroups_GetAllParents"(int8) > > > select * from InventGroups_GetAllParents(0::int8) > > === > > ERROR: function inventgroups_getallparents(bigint) does not exist > > HINT: No function matches the given name and argument types. You may > > need to add explicit type casts. > > If you double-quote your function name (or any identifier) when you > create it, you'll need to double-quote them when you call the > function as well. Try: > > select * from "InventGroups_GetAllParents"(0::int8) > > Michael Glaesemann > grzm myrealbox com > > > > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] indexing for left join
I have two tables:
TABLE ITEM
(
ITEM_PK serial,
RETAIL_PRICE numeric (7,2) NOT NULL,
...
PRIMARY KEY (ITEM_PK)
)
TABLE SERIAL_NO
(
SERIAL_NO_PK serial,
NO varchar (20) NOT NULL,
NAME varchar (20),
ITEM_FK integer NOT NULL,
PRIMARY KEY (SERIAL_NO_PK)
);
common query:
SELECT ITEM.ITEM_PK FROM ITEM
LEFT JOIN SERIAL_NO ON SERIAL_NO.ITEM_FK = ITEM.ITEM_PK
WHERE SERIAL_NO.NO ='WX1234'
GROUP BY ITEM.ITEM_PK
Table ITEM will eventually grow very big and SERIAL_NO will grow with
it. There will normally be zero or one SERIAL_NO per ITEM; few ITEMs
will have more than one SERIAL_NO.
I have created an index for SERIAL_NO.NO and one for SERIAL_NO.ITEM_FK
for the above query.
I ran an EXPLAIN:
HashAggregate (cost=1.06..1.06 rows=1 width=4)
-> Nested Loop (cost=0.00..1.06 rows=1 width=4)
Join Filter: ("inner".item_fk = "outer".item_pk)
-> Seq Scan on item (cost=0.00..0.00 rows=1 width=4)
-> Seq Scan on serial_no (cost=0.00..1.05 rows=1 width=4)
Filter: (("no")::text = 'WX1234'::text)
Sequential despite the indices? Or is this because the tables of my test
DB are virtually empty?
Many thanks in advance.
--
Regards,
Tarlika Elisabeth Schmitz
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] indexing for left join
> Sequential despite the indices? Or is this because the tables of my test > DB are virtually empty? This is it. PostgreSQL changes strategies with data load. Performance testing must be done on an approximation of the real data (both values and size). -- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] indexing for left join
T E Schmitz wrote: Sequential despite the indices? Or is this because the tables of my test DB are virtually empty? Yes - read up on analyse and column statistics for details. Oh, you've probably missed about vacuuming too. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Error calling self-made plpgsql function "function XYZ(bigint) does not exist"
On Thursday 19 January 2006 14:06, Juris wrote: >Argh... big thanks.. did not know what pgAdmin/PG have any >case-sensitive issues with functions... > >Also for fields it is relative... without dbl-quotes i could not query >anything (i am using "MySuperField"-like field names) That is usual behaviour for RDBMSes. They are case-insensitive if you use ALL_CAPS or small_letters only. But if you use CamelCasing (which is the canonical name of this style), you're actually the one who imposes a case-sensitiveness upon the DB interpreter. So, the advice usually goes to avoid CamelCasing like the plague. regards, -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] indexing for left join
Milorad Poluga wrote: Try to execute this modification of your query : SELECT ITEM.ITEM_PK FROM ITEM LEFT JOIN SERIAL_NO ON ( SERIAL_NO.ITEM_FK = ITEM.ITEM_PK AND SERIAL_NO.NO ='WX1234' ) GROUP BY ITEM.ITEM_PK SELECT ITEM.ITEM_PK FROM ITEM LEFT JOIN SERIAL_NO ON SERIAL_NO.ITEM_FK = ITEM.ITEM_PK WHERE SERIAL_NO.NO ='WX1234' GROUP BY ITEM.ITEM_PK For my small test DB both queries result in the same strategy. The query will be generated by an object relational interface depending on the user's search criteria. It will definitely be of the form I specified. I wanted to make sure that I have chosen the indices correctly. I am presuming, if the tables are big, that the index on SERIAL_NO.NO will be used for the WHERE clause and the one on SERIAL_NO.ITEM_FK for the join. -- Regards, Tarlika Elisabeth Schmitz ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] indexing for left join
Rod Taylor wrote: Sequential despite the indices? Or is this because the tables of my test DB are virtually empty? This is it. PostgreSQL changes strategies with data load. Performance testing must be done on an approximation of the real data (both values and size). Thanks for your responses. -- Regards, Tarlika Elisabeth Schmitz ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Error calling self-made plpgsql function "function XYZ(bigint) does not exist"
On Jan 20, 2006, at 1:45 , Leif B. Kristensen wrote: That is usual behaviour for RDBMSes. They are case-insensitive if you use ALL_CAPS or small_letters only. PostgreSQL is case-insensitive in the sense that it down-cases identifiers that are not double-quoted, e.g., MYSUPERFIELD -> mysuperfield MySuperField -> mysuperfield mysuperfield -> mysuperfield "MYSUPERFIELD" -> MYSUPERFIELD "MySuperField" -> "MySuperField" "mysuperfield" -> mysuperfield Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Error calling self-made plpgsql function "function XYZ(bigint) does not exist"
Jep... but pgAdmin_III inserts dbl-quotes, if there is some CamelCase... but from sql-console i can create objects w/ or w/o dbl-quotes, but in result i always get lower-cased objects.. Seems, i should `recreate` my db in lowercase.. it will take some time :( Thanks for advance. PS: problem here is that, then i initialize row in PHP, i do $row = $dbio->object($res)... and PHP variables/object_properties ar case-sensitive... 2006/1/20, Michael Glaesemann <[EMAIL PROTECTED]>: > > On Jan 20, 2006, at 1:45 , Leif B. Kristensen wrote: > > > That is usual behaviour for RDBMSes. They are case-insensitive if you > > use ALL_CAPS or small_letters only. > > PostgreSQL is case-insensitive in the sense that it down-cases > identifiers that are not double-quoted, e.g., > > MYSUPERFIELD -> mysuperfield > MySuperField -> mysuperfield > mysuperfield -> mysuperfield > > "MYSUPERFIELD" -> MYSUPERFIELD > "MySuperField" -> "MySuperField" > "mysuperfield" -> mysuperfield > > Michael Glaesemann > grzm myrealbox com > > > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
