pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
problem with RECORD in a stored procedure
3+ messages / 3 participants
[nested] [flat]

* problem with RECORD in a stored procedure
@ 2002-03-08 15:06 Dalton Shane <se401029@cs.may.ie>
  2002-03-08 16:16 ` Re: problem with RECORD in a stored procedure Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2002-03-08 17:53 ` Re: problem with RECORD in a stored procedure daq <daq@ugyvitelszolgaltato.hu>
  0 siblings, 2 replies; 3+ messages in thread

From: Dalton Shane @ 2002-03-08 15:06 UTC (permalink / raw)
  To: pgsql-sql


HI,

I have a strange problem with regard to the RECORD type. IN plpgsql language it 
doesn't seem to recognise the data type. Does anybody have any idea why? I've 
included a sample function and the terminal output to show what I mean. I've 
also included what should have gone into table orla.

Any help would be appreciated in sorting out this frustrating problem.

Shane

CREATE FUNCTION sinead() RETURNS integer AS '
	DECLARE
		a integer :=0;
		b RECORD;
		 
	
	BEGIN
		FOR b IN SELECT MIN(total) from results LOOP
			INSERT INTO orla(total) VALUES(b);
		END LOOP;
		
		RETURN a;
		
		
	END;
' LANGUAGE 'plpgsql';

-------------------------------------------------------------------------

ballina=# select sinead();
ERROR:  Attribute 'b' not found

--------------------------------------------------------------------------
ballina=# select MIN(total) from results;
 min 
-----
   2
(1 row)

---------------------------------------------------------------------------




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: problem with RECORD in a stored procedure
  2002-03-08 15:06 problem with RECORD in a stored procedure Dalton Shane <se401029@cs.may.ie>
@ 2002-03-08 16:16 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Stephan Szabo @ 2002-03-08 16:16 UTC (permalink / raw)
  To: Dalton Shane <se401029@cs.may.ie>; +Cc: pgsql-sql


On Fri, 8 Mar 2002, Dalton Shane wrote:

>
> HI,
>
> I have a strange problem with regard to the RECORD type. IN plpgsql language it
> doesn't seem to recognise the data type. Does anybody have any idea why? I've
> included a sample function and the terminal output to show what I mean. I've
> also included what should have gone into table orla.
>
> Any help would be appreciated in sorting out this frustrating problem.
>
> Shane
>
> CREATE FUNCTION sinead() RETURNS integer AS '
> 	DECLARE
> 		a integer :=0;
> 		b RECORD;
>
>
> 	BEGIN
> 		FOR b IN SELECT MIN(total) from results LOOP
> 			INSERT INTO orla(total) VALUES(b);

You need to specify an attribute of b to insert here (for example
b.min).

> 		END LOOP;
>
> 		RETURN a;
>
>
> 	END;
> ' LANGUAGE 'plpgsql';




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: problem with RECORD in a stored procedure
  2002-03-08 15:06 problem with RECORD in a stored procedure Dalton Shane <se401029@cs.may.ie>
@ 2002-03-08 17:53 ` daq <daq@ugyvitelszolgaltato.hu>
  1 sibling, 0 replies; 3+ messages in thread

From: daq @ 2002-03-08 17:53 UTC (permalink / raw)
  To: pgsql-sql


B is record type and you can't insert a record into a field.
You must rewrite your code like this:

FOR b IN SELECT MIN(total) as mintotal from results LOOP
                INSERT INTO orla(total) VALUES(b.mintotal);
END LOOP;

DAQ





^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2002-03-08 17:53 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-03-08 15:06 problem with RECORD in a stored procedure Dalton Shane <se401029@cs.may.ie>
2002-03-08 16:16 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-03-08 17:53 ` daq <daq@ugyvitelszolgaltato.hu>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox