Обсуждение: arrays
hi :> i wrote function to change array to setof record, because better is working with tuples . http://www.psycho.pl/public/src/pgsql/array.tar.bz2 i will very glade when you check this , because im afraid thed i make same mistakes .. (if its good ist could be added to contrib/array as one more funciton) its good to select like this (im writing from mem, not coping so sorry for mistakes in names) SELECT * FROM pg_catalog.pg_type t inner join pg_catalog.pg_namespace n ON (t.typnamespace=n.oid) WHERE pg_type = 'my_type' AND n.nspname IN (SELECT a FROM ( array_to_setof(current_schemas()) ) s (schema name)); declaration is setof record array_to_setof ( anyarray); i trayed created it like "any" array_to_setof( anyarray) but i had problem with select. i also trying write setof_to_array as aggregate, but i have problem with returns type , because aggregate cat have 'anyarray' as result ;( thx for your opionion. bye
ivan <iv@psycho.pl> writes: > declaration is setof record array_to_setof ( anyarray); > i trayed created it like "any" array_to_setof( anyarray) but i had problem > with select. i also trying write setof_to_array as aggregate, but i have > problem with returns type , because aggregate cat have 'anyarray' as > result ;( I think you want to declare it like this: create function array_to_setof(anyarray) returns setof anyelement as ... Joe Conway may already have written something like this --- look in his contrib modules. regards, tom lane
Tom Lane wrote: > Joe Conway may already have written something like this --- look in his > contrib modules. > I did write the function, but it isn't in any of the contrib modules. Here's a link to the thread: http://archives.postgresql.org/pgsql-hackers/2002-12/msg00453.php It was never committed due to dissatisfaction over the fact that table functions cannot have columns from other FROM clause items as arguments. Since then Peter pointed out that SQL99 specifically includes this functionality with the keyword UNNEST, and uses a LATERAL clause to allow referencing adjacent FROM items. Hopefully I'll find time to pursue implementing that for 7.5. Joe
On Sun, 27 Jul 2003, Tom Lane wrote: > ivan <iv@psycho.pl> writes: > > declaration is setof record array_to_setof ( anyarray); > > > i trayed created it like "any" array_to_setof( anyarray) but i had problem > > with select. i also trying write setof_to_array as aggregate, but i have > > problem with returns type , because aggregate cat have 'anyarray' as > > result ;( > > I think you want to declare it like this: > > create function array_to_setof(anyarray) returns setof anyelement as ... in this way i declared it : create function array_to_setof(anyarray) returns setof record as ' .. and its working because function create new type of record (as type of this anyarray) and then return each elem. In way back from typles to array , i have problem , because i need to create aggregate function . I think the function to arrays like this and function from contrib (array by Massimo Dal Zotto) should be in pg_catalog as std. and function to checking exists. i wrote function like : create function iv_exists (text, char) returns boolean, this function as 1 arg bring relation name, 2 arg is a type of this relation (is could be 'r' => table , 'c' => compose , 'f' function, 'C' => CASE , S s i etc like in pg_class.relkind, and i reserved 'M' to module and 'K' to class. But my way is very simple , but i did not find any function like this :/ > > Joe Conway may already have written something like this --- look in his > contrib modules. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >