How to Pass Custom Type Array to Postgres Function

How to pass an array of customs type to a PostgreSQL function

The tuple syntax can be produced using tuple_(), and using postgresql.array() you can produce array literals. Combining these you get:

from sqlalchemy import tuple_
from sqlalchemy.dialects.postgresql import array

class QueuePush(GenericFunction):

    name = "queue_push"

    @staticmethod
    def mapped_objects(**kwargs):
        input = array([tuple_(*t) for t in kwargs['arr_message_input']]
        return select(
            [
                Column('queue_push', BOOLEAN),
            ]
        ).select_from(
            func.queue_push( 
                # Eurgh, this cast handling is horrid, and uses the postgresql
                # specific cast syntax, but it'd require creating a UserDefinedType
                # or such just to use the proper cast method.
                input.op("::")(text("arr_message_input[]"))
            )
        ).alias(name="queue_push")

For advanced usage you might define a UserDefinedType and a bind_expression():

from sqlalchemy import tuple_
from sqlalchemy.types import UserDefinedType

class ArrMessageInput(UserDefinedType):

    def get_col_spec(self, **kw):
        return "arr_message_input"

    def bind_expression(self, val):
        return tuple_(*val)

and then

from sqlalchemy import tuple_
from sqlalchemy.dialects.postgresql import array, ARRAY

class QueuePush(GenericFunction):

    name = "queue_push"

    @staticmethod
    def mapped_objects(**kwargs):
        # For some reason the `bind_expression` is not applied to array elements,
        # even if using `array(..., type_=ArrMessageInput)`
        input = array([tuple_(*t) for t in kwargs['arr_message_input']])
        return select(
            [
                Column('queue_push', BOOLEAN),
            ]
        ).select_from(
            func.queue_push( 
                input.cast(ARRAY(ArrMessageInput))
            )
        ).alias(name="queue_push")

On the other hand if you are using psycopg2, you can rely on its adaptation of Python lists and tuples, and just pass the structure as a literal, cast to the correct type:

from sqlalchemy import literal
from sqlalchemy.dialects.postgresql import ARRAY

class QueuePush(GenericFunction):

    name = "queue_push"

    @staticmethod
    def mapped_objects(**kwargs):
        # `literal` is required, so that the cast doesn't apply SQLA type handling.
        # Note the use of Python's `tuple` instead of SQLAlchemy `tuple_`
        input = literal([tuple(t) for t in kwargs['arr_message_input']])
        return select(
            [
                Column('queue_push', BOOLEAN),
            ]
        ).select_from(
            func.queue_push( 
                input.cast(ARRAY(ArrMessageInput))
            )
        ).alias(name="queue_push")

Passing user-defined Type Array as input parameter to a function

Your variable assignment is wrong, you need to provide the array index to which you want to assign an element.

When you are calling a function returning a single value, you don't need a SELECT in PL/pgSQL, just assign the result:

do  
$$  
declare  
  v_key key_type[];  
  v_res TEXT;  
begin  
  v_key[1] := ('709R', 'Risk'); -- first array element
  v_key[2] := ('711X', 'Risk2'); -- second array element
  v_res := fn_det(v_key);  
  raise notice '%', v_res;  
end;  
$$  
language plpgsql;

Postgres: Passing custom types from Java to postgres function

I found 2 ways to pass the required value to the function:

1. It is difficult to create the string of the UDT if its complex. The easiest way is to do the reverse engineering. Create the UDT in the plpgsql and print it. This way you will get the string that need to be pass from Java. Now in Java code write a logic to create such string.
2. Another way is to pass the value in the json format and in the function, parse the json and construct the UDT by your own.

I choose the 2nd approach as it is easy to maintain.

Passing array of custom type to postgres function from node-pg and SQL injection

I am not familiar with node.js, but you could provide a string literal for your column p_photos instead of the ARRAY and ROW constructors (which are functions that need to be executed at the Postgres side!). Your query would look like this:

SELECT * FROM addphotos(
    p_placeid := 2210
  , p_permissiontypeid := 2
  , p_description := 'Party'
  , p_photos:='{"(,,.JPG,smart,6e8f74b2-4c14-4f40-ae19-8abae026a539)"
               ,"(,,.JPG,,c4e9f75f-25fa-4893-82f1-44c4791d58e5)"}'::phototable[]
    );

Should even work without explicit cast:

SELECT * FROM addphotos(
    p_placeid := 2210
  , p_permissiontypeid := 2
  , p_description := 'Party'
  , p_photos:='{"(,,.JPG,smart,6e8f74b2-4c14-4f40-ae19-8abae026a539)"
               ,"(,,.JPG,,c4e9f75f-25fa-4893-82f1-44c4791d58e5)"}'
    );

The fast and simple way to "rewrite" your syntax to a string literal: let Postgres do it:

SELECT array[row(null, null,'.JPG','smart','6e8f74b2-4c14-4f40-ae19-8abae026a539'),
             row(null, null,'.JPG',null,'c4e9f75f-25fa-4893-82f1-44c4791d58e5')]::phototable[]

Returns the string representation I used above:

How to pass custom typed multi-dimmensional array as a function parameter in PostgreSQL?

There are errors in your code:

Use round brackets for row/record types constants:

ARRAY[('qwe','asd'),('zxc','rty')]

Add doublequotes to function name:

select "addFriend"('Champs',ARRAY[('qwe','asd'),('zxc','rty')]::public."FriendDetails"[]);

Use singlequotes for string constants:

RETURN 'contact!';

Postgres cast Array to a Custom Type

It is hard to guess why someone would want to complicate his life with such a strange idea. Anyway, the type foo is a composite type with a single text[] element, so the literal should look like:

SELECT ROW(ARRAY['TEST_ONE'])::foo;

Maybe a domain would be more handy:

create domain foo as text[];
select array['test_one']::foo;

How to pass an array of JSON (or JSON array) to pg function to be INSERTed into a Table?

A JSON array (json) is different from a Postgres array of JSON values (json[]).

SELECT '[{"foo": "bar"}, {"foo1": "bar1"}]'::json;  -- JSON array

vs:

SELECT '{"{\"foo\": \"bar\"}","{\"foo1\": \"bar1\"}"}'::json[]  -- array of JSON

The first is an array nested inside a single JSON value, the second is an array of JSON values.

Postgres array of JSON (json[])

Your (fixed!) function:

CREATE OR REPLACE FUNCTION fun1(vja json[])
  RETURNS void
  LANGUAGE plpgsql AS
$func$
DECLARE
   v json;
BEGIN
   FOREACH v IN ARRAY vja
   LOOP
      INSERT INTO mytable(col1, col2, col3)
      VALUES(v ->> 'col1', (v ->> 'col2')::bool, (v ->> 'col3')::bool);
   END LOOP;
END
$func$;

Expects a call like this (note all the escaping for the json[] literal):

SELECT fun1('{"{\"col1\": \"turow1@af.com\", \"col2\": false, \"col3\": true}","{\"col1\": \"xy2@af.com\", \"col2\": false, \"col3\": true}"}'::json[]);

See:

• How to pass custom type array to Postgres function

But a single INSERT with json_populate_record() in a procedure beats looping in a function:

CREATE OR REPLACE PROCEDURE proc1(vja json[])
  LANGUAGE sql AS
$proc$
INSERT INTO mytable  -- target column list redundant in this particular case
SELECT r.*
FROM   unnest(vja) v, json_populate_record(NULL::mytable, v) r
$proc$;

See:

• When to use stored procedure / user-defined function?

Or simpler with the standard SQL variant in Postgres 14 or later:

CREATE OR REPLACE PROCEDURE proc1(vja json[])
BEGIN ATOMIC
INSERT INTO mytable
SELECT r.*
FROM   unnest(vja) v, json_populate_record(NULL::mytable, v) r;
END;

See:

• What does BEGIN ATOMIC ... END mean in a PostgreSQL SQL function / procedure?

Call (!):

CALL proc1('{"{\"col1\": \"turow1@af.com\", \"col2\": false, \"col3\": true}","{\"col1\": \"xy2@af.com\", \"col2\": false, \"col3\": true}"}'::json[]);

db<>fiddle here

JSON array (json)

Typically, you want to pass a JSON array like you tried.
So, now with json_populate_recordset():

CREATE OR REPLACE PROCEDURE proc2(vja json)
  LANGUAGE sql AS
$proc$
INSERT INTO mytable
SELECT * FROM json_populate_recordset(NULL::mytable, vja);
$proc$;

Or (Postgres 14):

CREATE OR REPLACE PROCEDURE proc2(vja json)
BEGIN ATOMIC
INSERT INTO mytable
SELECT * FROM json_populate_recordset(NULL::mytable, vja);
END;

Call (now you can use the value you originally tested with!):

CALL proc2('[
    {"col1": "turow1@af.com", "col2": false, "col3": true}, 
    {"col1": "xy2@af.com", "col2": false, "col3": true} 
    ]');

db<>fiddle here

Access array of custom type in a PostgreSQL C function

After minor fixes this code works:

PG_FUNCTION_INFO_V1(array_test);

Datum
array_test(PG_FUNCTION_ARGS)
{
    ArrayType *a = PG_GETARG_ARRAYTYPE_P(0);
    Datum  *datums;
    bool   *nulls;
    int     count;
    int16   elemWidth;
    Oid     elemType = ARR_ELEMTYPE(a);
    bool    elemTypeByVal, isNull;
    char    elemAlignmentCode;
    int     result = 0;
    HeapTupleHeader lt;
    short   field;

    if (ARR_NDIM(a) > 1)
        ereport(ERROR, (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR), errmsg("1-dimensional array needed")));

    get_typlenbyvalalign(elemType, &elemWidth, &elemTypeByVal, &elemAlignmentCode);
    deconstruct_array(a, elemType, elemWidth, elemTypeByVal, elemAlignmentCode, &datums, &nulls, &count);

    for (int i = 0; i < count; i++)
    {
        if (nulls[i])
        {
            result = -result;
        }
        else
        {
            lt = DatumGetHeapTupleHeader(datums[i]);

            field = DatumGetInt16(GetAttributeByNum(lt, 1, &isNull));
            if (!isNull)
                result += field;

            field = DatumGetInt16(GetAttributeByNum(lt, 2, &isNull));
            if (!isNull)
                result += field;
        }
    }

    PG_RETURN_INT32(result);
}

---

Related Topics