Обсуждение: plpgsql multidimensional array assignment results in array of text instead of subarrays

Поиск
Список
Период
Сортировка

plpgsql multidimensional array assignment results in array of text instead of subarrays

От
Michael Rasmussen
Дата:
Hello, I’m running 9.4.5 locally on my mac doing some plpgsql development.

I am trying to iterate through a multidimensional array using a foreach loop, as exampled in the documentation at http://www.postgresql.org/docs/9.4/static/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY.

Here is a simplified version of the function:

CREATE OR REPLACE FUNCTION create_table(
    new_table_schema character varying,
    new_table_name character varying,
    create_log boolean DEFAULT true,
    create_source boolean DEFAULT false
) RETURNS void AS
$BODY$
declare
    the_tables text[][];
    the_table text[];
begin
    -- Generate array of tables to create
    the_tables[1] := array[new_table_schema, new_table_name];
    
    if (create_source) then
        the_tables[2] := array[new_table_schema||'_source', new_table_name||'_source'];
    end if;

    RAISE NOTICE 'the_tables = %', the_tables;

    <<BIGLOOP>>
    foreach the_table slice 1 in array the_tables
    loop
        raise notice 'schema = %; table = %', the_table[1], the_table[2];
    end loop BIGLOOP;
    
end;
$BODY$
  LANGUAGE plpgsql;

When I run it, I get the following message output:

NOTICE:  the_tables = {"{mike,test}","{mike_source,test_source}"}

NOTICE:  schema = {mike,test}; table = {mike_source,test_source}


I am expecting:

NOTICE:  the_tables = {{'mike','test'},{'mike_source','test_source'}}

NOTICE:  schema = mike; table = test

NOTICE:  schema = mike_source; table = test_source


I suspect something is happening with the assignment operator :=, as those double quotes seem to indicate the subarrays are being cast to strings?


I tried casting during the assignment, i.e.  the_tables[1] := array[new_table_schema, new_table_name]::text[], but that had no effect.


Does anyone know what I might be doing wrong?


-- 
Michael Rasmussen
Sr. Data Engineer
Porch

Re: plpgsql multidimensional array assignment results in array of text instead of subarrays

От
Tom Lane
Дата:
Michael Rasmussen <michaelr@porch.com> writes:
> I am trying to iterate through a multidimensional array using a foreach loop, as exampled in the documentation at
http://www.postgresql.org/docs/9.4/static/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY.

> Here is a simplified version of the function:

> CREATE OR REPLACE FUNCTION create_table(
>     new_table_schema character varying,
>     new_table_name character varying,
>     create_log boolean DEFAULT true,
>     create_source boolean DEFAULT false
> ) RETURNS void AS
> $BODY$
> declare
>     the_tables text[][];
>     the_table text[];
> begin
>     -- Generate array of tables to create
>     the_tables[1] := array[new_table_schema, new_table_name];

>     if (create_source) then
>         the_tables[2] := array[new_table_schema||'_source', new_table_name||'_source'];
>     end if;

That's not going to work, because it's a type violation.
Multi-dimensional arrays in PG are not arrays of arrays.
(Maybe they should have been, but it's too late to change that.)
The only reason you don't get an immediate runtime error is that
plpgsql is so lax about type coercions, and a text value will
accept pretty much anything.

The right way to do what you're trying to do is array slice assignment.
Ideally, you'd write the above like this:

    -- Generate array of tables to create
    the_tables[1:1][1:2] := array[new_table_schema, new_table_name];

    if (create_source) then
        the_tables[2:2][1:2] := array[new_table_schema||'_source', new_table_name||'_source'];
    end if;

Unfortunately, that's got two problems: no one's ever gotten around to
making array slice assignment syntax work at all in plpgsql, and even if
it did, the second assignment requires extension of an already-existing
array value, which we don't currently support for multi-D cases (that's
the core array code's fault not plpgsql's fault).

Both of those things could probably be made to happen if anyone cared
to put in the work, but that won't help you in existing releases.

A workaround I've seen used is to create a composite type, so that
what you have is 1-D arrays of composite types of 1-D arrays:

create type textarray as (t text[]);

CREATE OR REPLACE FUNCTION create_table(
    new_table_schema character varying,
    new_table_name character varying,
    create_source boolean
) RETURNS void AS
$BODY$
declare
    the_tables textarray[];
    the_table textarray;
begin
    -- Generate array of tables to create
    the_tables[1] := row(array[new_table_schema, new_table_name])::textarray;

    if (create_source) then
        the_tables[2] := row(array[new_table_schema||'_source', new_table_name||'_source'])::textarray;
    end if;

    RAISE NOTICE 'the_tables = %', the_tables;

    <<BIGLOOP>>
    foreach the_table in array the_tables
    loop
        raise notice 'schema = %; table = %', the_table.t[1], the_table.t[2];
    end loop BIGLOOP;

end;
$BODY$
  LANGUAGE plpgsql;

select create_table('mike', 'test', true);

            regards, tom lane

Re: plpgsql multidimensional array assignment results in array of text instead of subarrays

От
"David G. Johnston"
Дата:
On Mon, Dec 28, 2015 at 4:05 PM, Michael Rasmussen <michaelr@porch.com> wrote:
Hello, I’m running 9.4.5 locally on my mac doing some plpgsql development.

I am trying to iterate through a multidimensional array using a foreach loop, as exampled in the documentation at http://www.postgresql.org/docs/9.4/static/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY.

Here is a simplified version of the function:

CREATE OR REPLACE FUNCTION create_table(
    new_table_schema character varying,
    new_table_name character varying,
    create_log boolean DEFAULT true,
    create_source boolean DEFAULT false
) RETURNS void AS
$BODY$
declare
    the_tables text[][];
    the_table text[];
begin
    -- Generate array of tables to create
    the_tables[1] := array[new_table_schema, new_table_name];
    
    if (create_source) then
        the_tables[2] := array[new_table_schema||'_source', new_table_name||'_source'];
    end if;

    RAISE NOTICE 'the_tables = %', the_tables;

    <<BIGLOOP>>
    foreach the_table slice 1 in array the_tables
    loop
        raise notice 'schema = %; table = %', the_table[1], the_table[2];
    end loop BIGLOOP;
    
end;
$BODY$
  LANGUAGE plpgsql;

When I run it, I get the following message output:

NOTICE:  the_tables = {"{mike,test}","{mike_source,test_source}"}

NOTICE:  schema = {mike,test}; table = {mike_source,test_source}


I am expecting:

NOTICE:  the_tables = {{'mike','test'},{'mike_source','test_source'}}

NOTICE:  schema = mike; table = test

NOTICE:  schema = mike_source; table = test_source


I suspect something is happening with the assignment operator :=, as those double quotes seem to indicate the subarrays are being cast to strings?


I tried casting during the assignment, i.e.  the_tables[1] := array[new_table_schema, new_table_name]::text[], but that had no effect.


Does anyone know what I might be doing wrong?



​I cannot adequately explain the behavior though you are likely correct that since the multi-dimensional array's type is text that the attempt to assign an array to an element converts the array to text instead of assigning the array.

Two suggestions:

1) Use the array modification operators defined here:
​to perform the modifications and reassign the entire result back to the variable.

​2) Create a composite type which can then be a simple component of a one-dimensional array.

I suggest doing both though either option might be workable alone if you wish to try things out...

David J.


Re: plpgsql multidimensional array assignment results in array of text instead of subarrays

От
"David G. Johnston"
Дата:
On Mon, Dec 28, 2015 at 4:39 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Michael Rasmussen <michaelr@porch.com> writes:
> I am trying to iterate through a multidimensional array using a foreach loop, as exampled in the documentation at http://www.postgresql.org/docs/9.4/static/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY.

create type textarray as (t text[]);

​or a more semantically meaning one...the use of the inner array is arguably a hack here meant to avoid the overhead and new type creation by assigning meaning to array slots.

​i.e.,​

create type table_with_schema (name text, schema text);

I would likewise attempt to do away with the outer array as well if the overall structure of the example follows reality closely enough.

​David J.

Re: plpgsql multidimensional array assignment results in array of text instead of subarrays

От
Tom Lane
Дата:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> ​or a more semantically meaning one...the use of the inner array is
> arguably a hack here meant to avoid the overhead and new type creation by
> assigning meaning to array slots.

Yeah, good point: it looks like Mike does not consider the columns of
the array to be interchangeable at all, so really he would be better
off modeling the data as you suggest.

            regards, tom lane

Re: plpgsql multidimensional array assignment results in array of text instead of subarrays

От
Michael Rasmussen
Дата:
Thank you all for your help.

I currently only have the two cases to handle, so I went with the below if-else statement which works how I expected.

    -- Generate array of tables to create
    if (create_source) then
    the_tables := array[[new_table_schema, new_table_name],[new_table_schema||'_source', new_table_name||'_source']];
    else
    the_tables := array[[new_table_schema, new_table_name]];
    end if;


-- 
Michael Rasmussen

Sr. Data Engineer
Porch






On 12/28/15, 3:51 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

>"David G. Johnston" <david.g.johnston@gmail.com> writes:
>> ​or a more semantically meaning one...the use of the inner array is
>> arguably a hack here meant to avoid the overhead and new type creation by
>> assigning meaning to array slots.
>
>Yeah, good point: it looks like Mike does not consider the columns of
>the array to be interchangeable at all, so really he would be better
>off modeling the data as you suggest.
>
>            regards, tom lane