FW: need help on stored procedures

#1

Quote:

Hi all,

I have written a sample procedure where i pass 2 arguments. Based on the
arguments i need to select few fields from a table. After selecting the
fields i have to display them. How do i return the selected fields. The
procedure is as follows

CREATE FUNCTION PP_ReadParameter(integer,varchar) RETURNS TEXT AS '
DECLARE
ParamId INTEGER;
ParamName TEXT;
IsFixEnum BIT;
IsExpandEnum BIT;
BEGIN
IF $1 IS NOT NULL THEN
SELECT INTO ParamId,ParamName,IsFixEnum,IsExpandEnum
AttributeId,AttributeName,IsFixEnum,IsExpandEnum
FROM Attributes
WHERE AttributeId = $1
ORDER BY AttributeId;

RETURN ''$1 successfull'';
END IF;
......
......
END;
' LANGUAGE 'plpgsql';

So when i say SELECT PP_ReadParameter(50,Null)......it should return the
ParamId,ParamName,....
But to check the working of the function i just return ''$1 successfull''
as i dont know how to return the tuple.

Please help me on this

With Best Regards
Pradeep Kumar P J

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

#2

On Wed, 7 Jul 2004, Pradeepkumar, Pyatalo (IE10) wrote:

Quote:

I have written a sample procedure where i pass 2 arguments. Based on the
arguments i need to select few fields from a table. After selecting the
fields i have to display them. How do i return the selected fields. The
procedure is as follows

CREATE FUNCTION PP_ReadParameter(integer,varchar) RETURNS TEXT AS '
DECLARE
ParamId INTEGER;
ParamName TEXT;
IsFixEnum BIT;
IsExpandEnum BIT;
BEGIN
IF $1 IS NOT NULL THEN
SELECT INTO ParamId,ParamName,IsFixEnum,IsExpandEnum
AttributeId,AttributeName,IsFixEnum,IsExpandEnum
FROM Attributes
WHERE AttributeId = $1
ORDER BY AttributeId;

RETURN ''$1 successfull'';
END IF;
......
......
END;
' LANGUAGE 'plpgsql';

So when i say SELECT PP_ReadParameter(50,Null)......it should return the
ParamId,ParamName,....
But to check the working of the function i just return ''$1 successfull''
as i dont know how to return the tuple.

There's a question of whether you expect this to return one row or
multiple rows. I'm guessing multiple rows, so...

Something of the general form:

CREATE TYPE newtype AS (ParamId ...);
CREATE FUNCTION PP_ReadParameter(integer,varchar) RETURNS setof newtype AS
'
DECLARE
rec newtype;
BEGIN
IF $1 IS NOT NULL THEN
FOR rec IN SELECT ParamId, ParamName, IsFixEnum, IsExpandEnum,
AttributeId, AttributeName
FROM Attributes
WHERE AttributeId = $1
ORDER BY AttributeId LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END IF;
...
END;' language 'plpgsql';

General Bits (http://www.varlena.com/varlena/GeneralBits/) and
techdocs.postgresql.org have some documents on returning sets from
functions that you might want to look at.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

#3

When you send the query to be parsed I presume you must somehow tell
which elements of it are parameters. How do you do this?

e.g.: SELECT name, phone FROM friends WHERE age > 25;

How would I write this if "name" and "25" must be parameters? Or if
the operator ">" should be a parameter or modifiable in another way?

(Is there a good tutorial site on the extended query language? It
seems there's almost no information about this on the net.)

Thanks,

Marc

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

#4

At 4:26 PM +0200 7/8/04, M. Bastin wrote:

Quote:

When you send the query to be parsed I presume you must somehow tell
which elements of it are parameters. How do you do this?

e.g.: SELECT name, phone FROM friends WHERE age > 25;

How would I write this if "name" and "25" must be parameters? Or if
the operator ">" should be a parameter or modifiable in another way?

Can "age > 25" as a whole be a parameter?

Thanks,

Marc

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

#5

"M. Bastin" <marcbastin (AT) mindspring (DOT) com> writes:

Quote:

At 4:26 PM +0200 7/8/04, M. Bastin wrote:
When you send the query to be parsed I presume you must somehow tell
which elements of it are parameters. How do you do this?
e.g.: SELECT name, phone FROM friends WHERE age > 25;
How would I write this if "name" and "25" must be parameters?

SELECT name, phone FROM friends WHERE $1 > $2;

Quote:

Or if
the operator ">" should be a parameter or modifiable in another way?

You cannot make an operator a parameter. It's not very clear what it
would mean to prepare a query in which some operators remain unknown ---
certainly the planner could not produce any useful plan for it.

Quote:

Can "age > 25" as a whole be a parameter?

Only if you are prepared to supply a boolean value for it at Bind time.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

FW: need help on stored procedures

comp.databases.postgresql.novice comp.databases.postgresql.novice

FW: need help on stored procedures - 07-08-2004 , 12:28 AM

Re: FW: need help on stored procedures - 07-08-2004 , 08:49 AM

Extended Query: Parse Command: syntax? - 07-08-2004 , 09:26 AM

Re: Extended Query: Parse Command: syntax? - 07-08-2004 , 09:53 AM

Re: Extended Query: Parse Command: syntax? - 07-09-2004 , 11:14 PM