PL/PGSQL :: Help Needed

[bholbrook]

Hello

I am a long time programmer of many languages, but I find that there is no useful documentation on PLPGSQL, so bare with me. I am writing a function that has multiple inputs and it doesnt liek my input params. Code posted below follwed by the call, followed by the error.

PGSQL Version 8.x from PG Lightning Admin

[b]FUNCTION[/b]
[code]CREATE or REPLACE FUNCTION public."createAgent"(
IN strname text,
IN intfranchiseid int4,
IN intteamid int4,
IN boolcreateschema boolean,
IN strschemaname text,
OUT intentityid int4)
RETURNS pg_catalog.int4 AS
$BODY$
DECLARE

intAgentId integer := 0;
intEntityHolder integer := 0;
arrAttributes text := '{}';

BEGIN

--query to insert agent into database
INSERT INTO public.rev2_agent (
"agent_franchise_id",
"agent_team_id",
"agent_name"
) VALUES ( intFranchiseId, intTeamId, strName );

--get new agent id
SELECT INTO intAgentId CURRVAL('public.rev2_selling_agent_seq');

--insert entity
INSERT INTO public.rev2_selling_entity (
"selling_entity_seller_id",
"selling_entity_schema"
) VALUES (
intAgentId,
strSchemaName
);

--get new entity id
SELECT INTO intEntityHolder CURRVAL('public.rev2_selling_entity_selling_entity_id_seq');

--check for create schema
IF boolCreateSchema = TRUE THEN

--create schema
EXECUTE 'CREATE SCHEMA' || strSchemaName;

--create config table in new schema
EXECUTE 'CREATE TABLE' || strSchemaName || '.config ('
|| 'config_id serial UNIQUE,'
|| 'config_attribute varchar(100) NOT NULL,'
|| 'config_value varchar(100),'
|| 'CONSTRAINT config_id_pkey PRIMARY KEY (config_id)';

--create array of attributes
arrAttributes[0] := 'prefix';
arrAttributes[1] := 'ftp_user';
arrAttributes[2] := 'ftp_pass';
arrAttributes[3] := 'ftp_host';
arrAttributes[4] := 'ftp_port';
arrAttributes[5] := 'ftp_time';
arrAttributes[6] := 'ftp_path';

--add fields to table
FOR i IN 0..6 LOOP

EXECUTE 'INSERT INTO' || strSchemaName || '.config'
|| '( config_attribute ) VALUES ("'
|| arrAttributes[i]
|| '")';


END LOOP;

END IF;

--return entity id
intEntityId := intEntityHolder;

END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;[/code]

[b]FUNCTION CALL[/b]
[code]SELECT public.createAgent('Brad Holbrook', 2, 0, FALSE, 'privatehomesellers');[/code]

[b]ERROR:[/b]
PostgreSQL Error Code: (1)
ERROR:  function public.createagent("unknown", integer, integer, boolean, "unknown") does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.

[neylitalo]

Hm... I always found the PL/pgSQL documentation quite good - look here? http://www.postgresql.org/docs/8.0/interactive/plpgsql.html

However, I have to ask - have you actually created the function? All the PL/pgSQL procedures I've written, I stored in an SQL file and then \i filename.sql with psql to actually create the function. If you've done that, you may want to change the " to ' in your CREATE FUNCTION statement - postgres uses ' to quote values rather than ".