current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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?

share|improve this question
    
Adding php tag would be wise. –  zero323 Sep 9 '13 at 21:42
    
I don't know... there's nothing of php on the question... –  Cheluis Sep 9 '13 at 23:31

1 Answer 1

up vote 2 down vote accepted

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:
Select rows such that names match elements of input array for pgsql function

share|improve this answer
    
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 2: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 3:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.