Skip site navigation (1) Skip section navigation (2)

Help with returning affected rows from a function

From: "Pradeepkumar, Pyatalo (IE10)" <Pradeepkumar(dot)Pyatalo(at)honeywell(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Help with returning affected rows from a function
Date: 2004-06-18 06:42:11
Message-ID: 77ED2BF75D59D1439F90412CC5B109740DBA4B9B@ie10-sahara.hiso.honeywell.com (view raw or flat)
Thread:
Lists: pgsql-novice
 Hi,

 I m porting stored procedures from SQL to PGSQL. I dont know much about
PGSQL. I have problems in returning affected rows from the function. I could
not find much information about this. I would really appreciate if anyone
can help me out with this.

This is the procedure i m trying to execute...

CREATE FUNCTION PP_ReadParameter(INTEGER,VARCHAR) RETURNS VARCHAR AS '
DECLARE 
	paramId ALIAS FOR $1;
	paramName ALIAS FOR $2;
	use_t Attributes%ROWTYPE;
BEGIN
	IF paramId IS NOT NULL THEN
		SELECT
			AttributeID AS ParameterID,
			AttributeName AS ParameterName,
			IsFixEnum,
			IsExpandEnum
		INTO use_t
		FROM Attributes
		WHERE AttributeID = paramID
		ORDER BY AttributeID;
		RETURN ''paramID successful'';
	ELSE
		IF paramName IS NOT NULL THEN
			SELECT
				AttributeID AS ParameterID,
				AttributeName AS ParameterName,
				IsFixEnum,
				IsExpandEnum
			INTO use_t
			FROM Attributes
			WHERE AttributeName = paramName
			ORDER BY AttributeID;
			RETURN ''paramName successful'';
		ELSE
			SELECT
				AttributeID AS ParameterID,
				AttributeName AS ParameterName,
				IsFixEnum,
				IsExpandEnum
			INTO use_t
			FROM Attributes
			ORDER BY AttributeID;
			RETURN ''paramID AND paramName not  successful'';
		END IF;
	END IF;
END;
' LANGUAGE 'plpgsql';

The Attributes table structure is 
		Attributes(AttributeID LONG INTEGER,
			AttributeName	VARCHAR (50),
			AttributeDataType VARCHAR (20),
			AttributeDataSource VARCHAR (20),
			AttributeDataSourceAttribute INTEGER,
			IsFixEnum Bit,
			IsExpandEnum Bit);
				
Just to chk the working of the function i m returning some text....actually
i want to return the rows that are affected and print them.

Thanks in advance.

> With Best Regards 
> Pradeep Kumar P J 
> 
> 

pgsql-novice by date

Next:From: Michael KleiserDate: 2004-06-18 10:28:25
Subject: Excuting SQL-Statements after error in same transaction ?
Previous:From: Lynna LandstreetDate: 2004-06-17 17:32:10
Subject: Re: Character encoding in database dumps

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group