unnest array of row type
От | seiliki@so-net.net.tw |
---|---|
Тема | unnest array of row type |
Дата | |
Msg-id | 20120112142557.A5F20F48F8F@m5.so-net.net.tw обсуждение исходный текст |
Ответы |
Re: unnest array of row type
|
Список | pgsql-general |
Hi! CREATE TYPE my_row_type AS (c1 SMALLINT,c2 TEXT); CREATE FUNCTION tmp_get_c1(my_row_type) RETURNS SMALLINT AS 'SELECT $1.c1' LANGUAGE SQL; CREATE FUNCTION tmp_get_c2(my_row_type) RETURNS TEXT AS 'SELECT $1.c2' LANGUAGE SQL; CREATE TABLE test (c1 SMALLINT,c2 TEXT,c3 TEXT); INSERT INTO test SELECT tmp_get_c1(r),tmp_get_c2(r),'x' FROM ( SELECT UNNEST(ARRAY[ROW(1::SMALLINT,'a'::TEXT),ROW(2::SMALLINT,'b'::TEXT)]) AS r ) s; I get error "record type has not been registered" from the previous INSERT. I have tested version 9.1 and have confirmed that PL/PGSQL "FOREACH ... IN ARRAY ... LOOP ... END LOOP;" does the job. BecauseI wonder "INSERT INTO ... SELECT ... FROM" being faster than "LOOP ... END LOOP;", I raise this question. Thank you in advance! CN
В списке pgsql-general по дате отправления: