Pass Array Literal to Postgresql Function

Pass array literal to PostgreSQL function

SQL NOT IN works with sets. Since you are passing an array, use <> ALL.

You have to be careful not to involve any NULL values with such an expression, because NULL <> anything never evaluates to TRUE and therefore never qualifies in a WHERE clause.

Your function could look like this:

CREATE OR REPLACE FUNCTION get_questions(vcode text[])
  RETURNS TABLE(id int, title text, code text)
  LANGUAGE sql AS
$func$
SELECT q.id, q.title, q.code
FROM   questions q
WHERE  q.code <> ALL ($1);
$func$;

Call with array literal:

SELECT * FROM get_questions('{qcode2, qcode2}');

Or with an array constructor):

SELECT * FROM get_questions(ARRAY['qcode2', 'qcode2']);

Or you could use a VARIADIC parameter:

CREATE OR REPLACE FUNCTION get_questions(VARIADIC vcode text[]) ...

... and pass a list of values:

SELECT * FROM get_questions('qcode2', 'qcode2');

Details:

• Return rows matching elements of input array in plpgsql function

Major points:

Using a simple SQL function since there is nothing in your question that would require the procedural elements of PL/pgSQL.

The input parameter is an array of text: text[]

To return multiple rows from your query use RETURNS TABLE for the return type.

Referring to the in parameter with the positional parameter $1 since referring by name was only introduced with version 9.2 for SQL functions (as opposed to plpgsql functions where this has been around for some versions now).

Table-qualify column names that would otherwise conflict with OUT parameters of the same name defined in the RETURNS clause.

LEFT JOIN unnest($1) / IS NULL

Faster for long arrays (> ~ 80 elements, it depends):

SELECT q.id, q.title, q.code
FROM   questions q
LEFT   JOIN unnest($1) c(code) USING (code)
WHERE  c.code IS NULL;

This variant (as opposed to the above) ignores NULL values in the input array.

Pass array as parameter to return all rows matching elements

One of various ways: pass an array literal to the = ANY construct:

SELECT * FROM tbl WHERE id = ANY ('{46995, 54262, 73166}');

See:

• Pass array literal to PostgreSQL function
• IN vs ANY operator in PostgreSQL

Postgres integer arrays as parameters?

See: http://www.postgresql.org/docs/9.1/static/arrays.html

If your non-native driver still does not allow you to pass arrays, then you can:

• pass a string representation of an array (which your stored procedure can then parse into an array -- see string_to_array)

  CREATE FUNCTION my_method(TEXT) RETURNS VOID AS $$ 
  DECLARE
         ids INT[];
  BEGIN
         ids = string_to_array($1,',');
         ...
  END $$ LANGUAGE plpgsql;
  

  then

  SELECT my_method(:1)
  

  with :1 = '1,2,3,4'

• rely on Postgres itself to cast from a string to an array

  CREATE FUNCTION my_method(INT[]) RETURNS VOID AS $$ 
         ...
  END $$ LANGUAGE plpgsql;
  

  then

  SELECT my_method('{1,2,3,4}')
  

• choose not to use bind variables and issue an explicit command string with all parameters spelled out instead (make sure to validate or escape all parameters coming from outside to avoid SQL injection attacks.)

  CREATE FUNCTION my_method(INT[]) RETURNS VOID AS $$ 
         ...
  END $$ LANGUAGE plpgsql;
  

  then

  SELECT my_method(ARRAY [1,2,3,4])
  

Malformed array literal when selecting into custom type within a postgresql function

The solution is to assign decomposed values:

CREATE OR REPLACE FUNCTION net.get_route(beg_ int8, end_ int8, mida int8[], dist int4)
  RETURNS route_part AS
$func$
DECLARE
   routerec route_part;
BEGIN
   SELECT INTO routerec * FROM net.get_route_part_dist(beg_, end_, dist);
   RETURN routerec;
END
$func$ LANGUAGE plpgsql;

Since routerec is a row type (composite type), The columns of the SELECT list must match the columns of the row type. The form you had would attempt to fit the value (as a whole) returned by net.get_route_part_dist() into the first column of routerec.

Quoting the manual:

If a row or a variable list is used as target, the query's result
columns must exactly match the structure of the target as to number
and data types

Postgres tries to fit your composite type (or rather its text representation) into bigint[], the first column of the composite type routerec. The error message you quoted is the consequence.

Explanation in the manual:

If the expression's result data type doesn't match the variable's data
type, the value will be coerced as though by an assignment cast (see
Section 10.4). If no assignment cast is known for the pair of data
types involved, the PL/pgSQL interpreter will attempt to convert the
result value textually, that is by applying the result type's output
function followed by the variable type's input function. Note that
this could result in run-time errors generated by the input function,
if the string form of the result value is not acceptable to the input function.

This can be confusing and fooled the first time as well. The distinction seems necessary since INTO allows assigning a list of target variables at once.

The bottom line is this: Decompose row types with SELECT * FROM ... when assigning to a row / record / composite type with INTO. Else it will be assigned to the first target column as a whole.

Avoid these inefficient forms:

Like you commented:

SELECT INTO routerec (net.get_route_part_dist(beg_, end_, dist)).nodea
, (net.get_route_part_dist(beg_, end_, dist)).edgea
, (net.get_route_part_dist(beg_, end_, dist)).geom;

Or, less verbose, but equally inefficient:

SELECT INTO routerec (net.get_route_part_dist(beg_, end_, dist)).*;

Each would evaluate the function multiple times - as opposed to:

SELECT INTO routerec * FROM net.get_route_part_dist(beg_, end_, dist)

Related:

• Use of custom return types in a FOR loop in plpgsql
• Passing array of a composite type to stored procedure

Simple alternative

The simple alternative for your simple case: direct assignment (without INTO):

  routerec := net.get_route_part_dist(beg_, end_, dist);
  RETURN routerec;

Simple assignment only allows a single target to begin with.

Or return the result directly:

  RETURN net.get_route_part_dist(beg_, end_, dist);

Constructing array literal as input for PL/pgSQL function

The array literal should look like this:

select jobs_v0.insert_job(
          'QmTXzATwNfgGVukV1fX2T6xw9f6LAYRVWpsdXyRWzUR2H9',
          '{"(main,\"\\\\xdeadbeef\")"}'
       );

But the bug is in the function code:

INSERT INTO jobs_v0.programs(job, handle, package)
SELECT job_id, * FROM unnest(programs)
RETURNING * INTO inserted_programs;

The * in RETRUNING * does refer to all columns of jobs_v0.programs, not to all columns of jobs_v0.insertable_program, as you seem to expect.

Besides, inserted_programs cannot be of an array type, it would have to be of type jobs_v0.insertable_program and can contain only a single result. If the INSERT inserts several rows, only the first one will be returned.

---

Related Topics