Jump to content


Photo

PL/PGSQL :: Help Needed


  • Please log in to reply
1 reply to this topic

#1 bholbrook

bholbrook
  • Members
  • PipPipPip
  • Advanced Member
  • 31 posts

Posted 26 September 2006 - 05:02 PM

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

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

FUNCTION CALL
SELECT public.createAgent('Brad Holbrook', 2, 0, FALSE, 'privatehomesellers');

ERROR:
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.


#2 neylitalo

neylitalo
  • Staff Alumni
  • Advanced Member
  • 1,854 posts
  • LocationMichigan, USA

Posted 01 October 2006 - 06:04 AM

Hm... I always found the PL/pgSQL documentation quite good - look here? http://www.postgresq...ve/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 ".
http://nealylitalo.net - My personal website, and home of The Netizen's Journal.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users