Re: Name/Value array to table row - Mailing list pgsql-sql

Hi David,

Yes, I was a bit vague on my description of the array. This was deliberate because it's still undecided. I am doing an Oracle package convert with a SIGNATURE of :

    PROCEDURE dManageRecord

      (   pdatacapturekey_i      IN   tdatacapture.datacapturekey%TYPE,

          pcolumn_values_i       IN   XPORTAL_DATACAPTURE_TABLE,

          pScopes_i IN   CLOB DEFAULT NULL,

          pUserProfileXML_i IN   XMLTYPE DEFAULT NULL

      ) IS

t_column_values         XPORTAL_DATACAPTURE_TABLE;

--snip---

later t_column_values is  accessed thusly:

        FOR indx IN t_column_values.FIRST .. t_column_values.LAST LOOP

            v_col_name  := UPPER(t_column_values(indx).col_name);

            v_col_value := t_column_values(indx).col_value;

This is how that array is defined:

CREATE OR REPLACE TYPE QSN_APP."COLUMN_NAME_VALUE_OBJECT"                                          AS OBJECT (

  col_name           VARCHAR2(30),

  col_value          VARCHAR2(4000)

)

/

CREATE OR REPLACE TYPE QSN_APP."XPORTAL_DATACAPTURE_TABLE"  as table of COLUMN_NAME_VALUE_OBJECT

/

So........... let me expand my original question to also ask what datatype you would

suggest for pcolumn_values_i keeping in mind that ultimately the name/value pairs correspond to Table Column names.

Thanks!

Mike

On Mon, Dec 14, 2015 at 5:31 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Mon, Dec 14, 2015 at 6:20 PM, Michael Moore <michaeljmoore@gmail.com> wrote:

I have an array that is a name value pair where the name is the name of a column in a table. There are about 300 columns. I want to use all of the name value pairs to construct the table row. I've thought about using jsonb or hstore. I'm sure I could write some ugly code to do this, but what is the BEST way to go about this?

I.E 

ARRAY

name |value

-----------

animal | 'cat'

lbs      | 20

create table animals (animal text, lbs number);

​I cannot interpret your pseudo-code shorthand array stuff but I suspect the "table row construction" ​would be most simply implemented using "json_populate_record(...)" and preceding code can focus on transforming your array into the equivalent json.  Using the array directly would likely require you to construct dynamic SQL in a plpgsql function.

Given my confusion regarding the form of your array it is not possible to advise on how such a conversion (to json) function/query would look.  There are considerable array and json functions that should make doing this step reasonably straight-forward.  unnest() may enter into the picture.

David J.