[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"
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
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
Re: [SQL] to count no of columns in a table
Create view/storedproc on pg_class & pg_attribute tables 2006/2/16, AKHILESH GUPTA <[EMAIL PROTECTED]>: > hi all, > i just want to know one thing that is there any function in PGSQL which > gives me the total number of columns in a table. > OR > just like we are using count(*), it gives us total number or rows in a > table, just like i want to know the total number of columns present in the > table > plz help me it's urgent > THANKS IN ADVANCE! > > -- > Thanks & Regards, > Akhilesh > DAV Institute of Management > Faridabad(Haryana)-INDIA > > "FAILURES CAN BE FORGIVEN BUT AIMING LOW IS A CRIME" > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] hi i need to encrypt one field in a table
For SQL-passwords usually use hashes... 2006/5/30, Penchalaiah P. <[EMAIL PROTECTED]>: Hello, I want to create a table with an encrypted field for a password entry, so that you can see only . And can I encrypt fields which datatype is an integer or a timestamp? Hi pls let me know this……….. Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this mail from your records. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] pl/pgsql problem with return types
Hello! i have problem with pl/pgsql function; === ERROR: wrong record type supplied in RETURN NEXT CONTEXT: PL/pgSQL function "tests" line 6 at return next === -- Function: tests(int8) -- DROP FUNCTION tests(int8); CREATE OR REPLACE FUNCTION tests(int8) RETURNS SETOF pages AS $BODY$DECLARE PRECORD; BEGIN FOR P IN select pageid from pages LOOP RETURN NEXT P; END LOOP; RETURN; END;$BODY$ LANGUAGE 'plpgsql' STABLE; ALTER FUNCTION tests(int8) OWNER TO diglat_web; -- Table: pages -- DROP TABLE pages; CREATE TABLE pages ( pageid int8 NOT NULL, ppageid int8 NOT NULL DEFAULT 0, name varchar(100), status int4 DEFAULT 0, CONSTRAINT pages_pkey PRIMARY KEY (pageid), CONSTRAINT pages_in_pages_fkey FOREIGN KEY (ppageid) REFERENCES pages (pageid) ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT pages_uniq UNIQUE (pageid, ppageid) ) WITH OIDS; Actualy function is correct... and the same code run successfully on other table.. there is the problem ? Thanks ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] pl/pgsql problem with return types
Yep.. i have solved this problem by specifying the correct return type and variable type (should be the same) but as said, in some cases pl_exec executes with type conversion. as real example - i have : return type = SETOF new type "category_node(catid, pcatid)" variable = R, SR -> RECORD and FOR R IN select * from ... LOOP RETURN NEXT R; /* add RECORD to SETOF_of_CATEGORY_NODE */ and works... the problem seems is teh same - on altered tables there is some porblem with this :) Actualy - return type & variable in "RETURN NEXT" must be the same type. // Solved John DeSoi wrote: On Mar 11, 2005, at 5:54 AM, Juris Zeltins wrote: FOR P IN select pageid from pages This way you are only getting the pageid column. I think what you want is FOR P in select * from pages so that P contains the complete pages record. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
