Thread: array_map not SQL accessible?

Hi,

I'm wondering why don't we expose the array_map() function to the SQL
level.  As it happens, I need to use it in the pg_dump support for TOAST
reloptions.

Why?  Well, TOAST reloptions are stored in the pg_class tuple of the
TOAST table, so when I extract them directly, it looks like a simple
array of normally-named reloptions.  Like this:

alvherre=# select c.oid,c.relname, c.reloptions, tc.reloptions
alvherre-# from pg_class c join pg_class tc on c.reltoastrelid = tc.oid
alvherre-# where c.relname = 'foo'; oid  | relname |   reloptions    |   reloptions    
-------+---------+-----------------+-----------------48372 | foo     | {fillfactor=10} | {fillfactor=15}
(1 fila)

So I need the second array to look like this instead:

toast.fillfactor=15

The easiest way to do that that I can see is using array_map and a
function that prepends "toast." to each element.

So, can I just go ahead and try to expose it for this usage?

-- 
Alvaro Herrera                 http://www.amazon.com/gp/registry/CTMLCN8V17R4
You liked Linux a lot when he was just the gawky kid from down the block
mowing your lawn or shoveling the snow. But now that he wants to date
your daughter, you're not so sure he measures up. (Larry Greenemeier)

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> I'm wondering why don't we expose the array_map() function to the SQL
> level.

It requires some notion of "reference to function", which doesn't really
exist in SQL.  (Please don't say you're going to pass it a function
OID.)

> As it happens, I need to use it in the pg_dump support for TOAST
> reloptions.

Maybe something involvingARRAY(SELECT foo(x) FROM UNNEST(arrayvariable) x)
        regards, tom lane

Tom Lane wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> > I'm wondering why don't we expose the array_map() function to the SQL
> > level.
> 
> It requires some notion of "reference to function", which doesn't really
> exist in SQL.  (Please don't say you're going to pass it a function
> OID.)

regproc maybe?

> > As it happens, I need to use it in the pg_dump support for TOAST
> > reloptions.
> 
> Maybe something involving
>     ARRAY(SELECT foo(x) FROM UNNEST(arrayvariable) x)

Hmm, I'll have a look at this.

-- 
Alvaro Herrera                               http://www.PlanetPostgreSQL.org/
"Linux transformó mi computadora, de una `máquina para hacer cosas',
en un aparato realmente entretenido, sobre el cual cada día aprendo
algo nuevo" (Jaime Salinas)

Alvaro Herrera wrote:
> Tom Lane wrote:

> > > As it happens, I need to use it in the pg_dump support for TOAST
> > > reloptions.
> > 
> > Maybe something involving
> >     ARRAY(SELECT foo(x) FROM UNNEST(arrayvariable) x)
> 
> Hmm, I'll have a look at this.

It seems there's something wrong here.

alvherre=# select c.oid,tc.oid,c.relname, c.reloptions, array(select 'toast.' || x from unnest(tc.reloptions) x) from
pg_classc join pg_class tc on c.reltoastrelid = tc.oid where c.relname = 'foo';
 
-[ RECORD 1
]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
oid        | 16395
oid        | 16398
relname    | foo
reloptions | {fillfactor=10,bogusopt=15}
?column?   |
{toast.fillfactor=20,toast.\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F\x7F}

To reproduce easily:

create table text (a text[]);
insert into text values ('{fillfactor=10,bogusval=20}');
select array(select 'foobar.' || x from unnest(a) x) from text;

-- 
Alvaro Herrera       Valdivia, Chile   ICBM: S 39º 49' 18.1", W 73º 13' 56.4"
"I can see support will not be a problem.  10 out of 10."    (Simon Wittber)
(http://archives.postgresql.org/pgsql-general/2004-12/msg00159.php)

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> It seems there's something wrong here.

Ah, it's a bug in array_unnest: if its argument is toasted, it detoasts
it into function-local memory, and then tries to keep a pointer to that
across calls.  Boo.  Will fix.
        regards, tom lane