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

help / color / mirror / Atom feed

INSERTing values from usertypes how-to?
2+ messages / 2 participants
[nested] [flat]

* INSERTing values from usertypes how-to?
@ 2006-04-27 15:43 Mario Splivalo <mario.splivalo@mobart.hr>
  2006-04-27 16:34 ` Re: INSERTing values from usertypes how-to? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Mario Splivalo @ 2006-04-27 15:43 UTC (permalink / raw)
  To: pgsql-sql

Comming back after SELECTing into usertypes, is there a nice way to
insert data into table from usertype, in a nice way?

For instance, I have table like this:

CREATE TABLE tblA (
	id int4,
	key varchar,
	value varchar
)

Then, in my function, I do this:

CREATE FUNCTION testInsert()
RETURNS void
AS
$BODY$
DECLARE
	insert_tblA tblA;

	insert_tblA.id = get_next_id_or_something();
	insert_tblA.key = get_user_key_or_something();
	insert_tblA.value = get_some_value();

	INSERT INTO tblA (
		id,
		key,
		value)
	VALUES (
		insert_tblA.id,
		insert_tblA.key,
		insert_tblA.value
	);

END
$BODY$ LANGUAGE 'plpgsql';


Now, in this particular example it seems stupid first to populate
usertype and then insert the data from it to the tblA table, but in my
actuall function that makes sense (the function is quite large for
posting here). Now, since sometimes I have 30 columns in a table, I
tried to find a way to simply 'pour' the usertype into the table. I
tried something like:

	INSERT INTO tblA (insert_tblaA);

but postgres complains (and I guess I expeted that) with an error saying
that tblA.id is of type int4, and I'm trying to insert value of type
tblA. So, is there a way to map all those type-members in a way so that
one could easily INSERT those into table?

	Mario




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

* Re: INSERTing values from usertypes how-to?
  2006-04-27 15:43 INSERTing values from usertypes how-to? Mario Splivalo <mario.splivalo@mobart.hr>
@ 2006-04-27 16:34 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2006-04-27 16:34 UTC (permalink / raw)
  To: mario.splivalo@mobart.hr; +Cc: pgsql-sql

Mario Splivalo <mario.splivalo@mobart.hr> writes:
> Comming back after SELECTing into usertypes, is there a nice way to
> insert data into table from usertype, in a nice way?

Try
	insert into tblA values (insert_tblA.*);

This works in CVS HEAD at least, not sure how far back.

			regards, tom lane



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

end of thread, other threads:[~2006-04-27 16:34 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-27 15:43 INSERTing values from usertypes how-to? Mario Splivalo <mario.splivalo@mobart.hr>
2006-04-27 16:34 ` 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