How to insert composite type plv8

Question

I have created a db structure with a composite type array. I would like to insert some values using plv8, but I can't find the correct structure to insert this array.

The error I'm getting is:

ERROR:  malformed record literal: "[object Object]"
SQL state: 22P02
DETAIL:  Missing left parenthesis.

I know inserting with ARRAY[ROW()] works, but then I would have to make a long string of the entire array that has to be inserted. Below is the structure I made and the insert I have tried. What's the right way to insert this array/object with plv8?

CREATE TYPE mydb.langpath AS
(lang text,
path text[]);

CREATE TABLE mydb.paths
(
info text NOT NULL,
langpath mydb.langpath[],
 )

do language plv8 $$ 
var plan=plv8.prepare('INSERT INTO mydb.paths( info, langpath) VALUES($1, $2)', ['text','mydb.langpath[]'] );
var params=new Array();
params.push('infotext');
var arr=[]; /*this structure is normally created by other functions*/
var pts=[];
pts.push('abc');
pts.push('def');
arr.push({lang:'EN',path:pts}); /*arr can have more values.*/
params.push(arr);
plan.execute(params);
$$

Geir Bostad · Accepted Answer · 2016-04-18T16:56:56.407

I was only partially able solve what you want (see code below). I was able to insert into a composite containing an array(path as array), but not insert into an array of composites (langpath as array).

In plain sql this looks possible - if my query is correct=) (see code at bottom). So this might be an issue with PLV8 prepare/execute.

I'd personally split this model into multiple tables and let the database do what it is good at. Or maybe just use json at some level, it is better for nested structures, and you already have a javascript engine available (PLV8). Postgresql (as of version 9.5) has gotten quite good support for json without PLV8. From 9.2 to 9.4 there were only limited support for manipulating json. Now you can also do some basic indexing of documents (using GIN)

Here is the full code of what I was able to do, note the missing array for langpath:

-- Installed plv8 via apt (Ubuntu 15.10) for Postgresql9.5
--CREATE EXTENSION plv8;   
DROP SCHEMA IF EXISTS mydb CASCADE;
CREATE SCHEMA mydb;

CREATE TYPE mydb.langpath AS (
    lang text,
    path text[]
);

CREATE TABLE mydb.paths (
    info text NOT NULL,
    langpath mydb.langpath
);

DO LANGUAGE plv8 $$ 
var plan=plv8.prepare("INSERT INTO mydb.paths(info, langpath) VALUES($1, $2)", ['text', 'mydb.langpath'] );
var params=new Array();
params.push('infotext');
var pts=[];
pts.push('englishPath1');
pts.push('englishpath2');
params.push({lang:"EN",path:pts});
plan.execute(params);
$$

SELECT path FROM (SELECT (langpath).* FROM mydb.paths) a;
/*
row  |  path (text[])
-----|-------------------------------
1    |  {englishPath1,englishpath2}
*/

SELECT path[1] FROM (SELECT (langpath).* FROM mydb.paths) a;
/*
row  |  path (text)
-----|-------------------------------
1    |  englishPath1
*/

For completeness, here is the code to test inserting via plain SQL to your original table definition, along with an example query to select values from the nested arrays.

INSERT INTO mydb.paths(info, langpath) VALUES('infotext', 
    ( array[
            row('EN', array['englishPath1', 'englishPath2']),
            row('FR', array['frenchPath1', 'frenchPath2'])
           ]::mydb.langpath[]
    )
);

SELECT path[2] FROM (
    SELECT (langpath).* FROM (
        SELECT langpath[1] FROM mydb.paths
    ) a
) b

As I feared what I was trying with plv8 is not possible, therefore I decided to change my db structure as recommended so I do not need arrays of composite typed objects. — DavidVdd, Apr 19 '16 at 08:06

How to insert composite type plv8

1 Answers1