pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Passing composite values to functions
2+ messages / 2 participants
[nested] [flat]

* Passing composite values to functions
@ 2004-02-13 15:24  Jatinder Sangha <Jatinder@sanghaservices.co.uk>
  0 siblings, 1 reply; 2+ messages in thread

From: Jatinder Sangha @ 2004-02-13 15:24 UTC (permalink / raw)
  To: pgsql-sql

Hi,
 
I'm having trouble passing a composite variable to a function.
 
I have a composite as follows:
 
    declare userRecord dnaUsers%ROWTYPE;
 
and I manipulate it like this:
 
    select into userRecord * from dnaUsers where etc;
    userRecord.userid := 'jatinder'; --etc
 
 
I would like to then insert this record into the table (kind of like this):
 
    insert into dnaUsers select userRecord;
    insert into dnaUsers select userRecord.*;
    insert into dnaUsers values (userRecord);
    insert into dnaUsers values (userRecord.*);
 
All of the above generate various errors relating to "userRecord" not being
a table?
 
I can't find any examples of this in the archives or the documentation. Is
this even possible?
 
I also, haven't found a way to pass my "userRecord" to a function, eg:
 
create or replace function isUserActive (dnaUsers)
returns boolean
as '
    do something with $1;
    return true;
' language plpgsql;

The problem here is that I can't pass userRecord to the new function:
 
    isActive := isUserActive (userRecord);
 
Again, the errors appear to be about "userRecord" not being a table?
 
So, is it even possible to use composite records in this manner? All of the
examples I've seen, seem to be about passing a row of a direct select
statement as a composite argument to a function, and not about passing a
locally declared composite variable.
 
Can anyone help correct my understanding of what I can and can't do with
composites?
 
Thanks,
--Jatinder

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

* Re: Passing composite values to functions
@ 2004-02-15 18:38  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Jatinder Sangha <Jatinder@sanghaservices.co.uk>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2004-02-15 18:38 UTC (permalink / raw)
  To: Jatinder Sangha <Jatinder@sanghaservices.co.uk>; +Cc: pgsql-sql

"Jatinder Sangha" <Jatinder@sanghaservices.co.uk> writes:
> I'm having trouble passing a composite variable to a function.

AFAIK, plpgsql doesn't support this --- it doesn't have any way to pass
a row or record variable as a parameter to the main executor.  Possibly
this will get fixed someday; I don't know of anyone working on it now
though.

			regards, tom lane



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

end of thread, other threads:[~2004-02-15 18:38 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-13 15:24 Passing composite values to functions Jatinder Sangha <Jatinder@sanghaservices.co.uk>
2004-02-15 18:38 ` Tom Lane <tgl@sss.pgh.pa.us>

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