0

I'm trying to maintain a Php application with a PostgreSQL database. At one point, a stored procedure is called, lets say function_x and inside function_x, function_y is called; function_y is passed a variable named parameter_1, and the definition of parameter_1 is:

parameter_1 numeric[][3] := {};

I'm trying to do a select function_y directly on the command line (or pgadmin) but I'm having problems passing an empty array into the function. according to the docs you have to use variadic but so I tried:

select function_y(581, 'CPN-00000000001-0000', 'TPN-00000000001-0001', 100, 2013, variadic arr := array[]);

But I got this error:

ERROR:  cannot determine type of empty array

I tried different approaches but nothing works. How can I pass a multidimensional array as a parameter at a query?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Cheluis
  • 1,402
  • 3
  • 22
  • 51

1 Answers1

2

1) You can, but you do not have to use VARIADIC parameters for array variables. You'd have to use it in the declaration of the function, not in the call, though.

2) Postgres array variables ignore dimensions in the definition. I quote the manual here:

The current implementation does not enforce the declared number of dimensions either. Arrays of a particular element type are all considered to be of the same type, regardless of size or number of dimensions. So, declaring the array size or number of dimensions in CREATE TABLE is simply documentation; it does not affect run-time behavior.

3) This is invalid syntax:

parameter_1 numeric[][3] := {};

Single quotes are required:

parameter_1 numeric[][3] := '{}';

Which is effectively the same as

parameter_1 numeric[] := '{}';

More details, code examples and links in this closely related answer:
Return rows matching elements of input array in plpgsql function

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Isn't it usually better to use `array[...]` to avoid all the quoting nonsense that the string version (`'{...}'`) can lead to? – mu is too short Sep 10 '13 at 02:53
  • @muistooshort: It depends. If single quotes can be part of the literal, you could use dollar-quoting instead. Or use `quote_literal()`. In the case at had, for the empty array, my given answer should be simplest. – Erwin Brandstetter Sep 10 '13 at 03:06