Thread: 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
Re: Error calling self-made plpgsql function "function XYZ(bigint) does not exist"
From
Michael Glaesemann
Date:
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
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 <grzm@myrealbox.com>: > > 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 > > > >
Re: Error calling self-made plpgsql function "function XYZ(bigint) does not exist"
From
"Leif B. Kristensen"
Date:
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
Re: Error calling self-made plpgsql function "function XYZ(bigint) does not exist"
From
Michael Glaesemann
Date:
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
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 <grzm@myrealbox.com>: > > 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 >
Hi, Juris, Juris wrote: > Seems, i should `recreate` my db in lowercase.. it will take some time :( Maybe you can modify it "inplace" using ALTER TABLE "OldName" RENAME TO newname; This might even be scripted, select * from pg_tables where schemaname='your schema' gives a list of all tables. Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org