Jump to content


This topic is now archived and is closed to further replies.


PL/PGSQL :: Help Needed

Recommended Posts


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

[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

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


--query to insert agent into database
INSERT INTO public.rev2_agent (
) 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 (

--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

--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]
|| '")';



--return entity id
intEntityId := intEntityHolder;

LANGUAGE 'plpgsql' VOLATILE;[/code]

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

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.

Share this post

Link to post
Share on other sites
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 ".

Share this post

Link to post
Share on other sites


Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.