0

I created a function that returns an 

text[]

which works as it should

CREATE OR REPLACE FUNCTION another_function_array()
RETURNS text[] AS $$
SELECT array_agg(column_name::text) 
FROM information_schema.columns         
WHERE table_schema = 'abc' AND table_name = 'xyz' 
$$
LANGUAGE SQL;

Now I want to use that function in another function:

CREATE OR REPLACE FUNCTION get_array()
RETURNS VOID AS $$
DECLARE 
arr text[] := another_function_array()
$$ LANGUAGE plpgsql;

and it gives me the output

Error: ERROR: syntax error at end of input Position: 107 SQLState: 42601 ErrorCode: 0

Ok so the 

;

at the end of

arr text[] := another_function_array()

is missing was my first guess but when I add it I get the error

java.lang.ArrayIndexOutOfBoundsException Error occurred in: CREATE OR REPLACE FUNCTION get_array() RETURNS VOID AS $$ DECLARE arr text[] := another_function_array()

and now I'm a little bit confused cause I must be missing something fundamental here

Nakilon
  • 34,866
  • 14
  • 107
  • 142
aldr
  • 838
  • 2
  • 19
  • 33
  • where's the code after declare? the rest of the body? – Vao Tsun Mar 16 '17 at 12:39
  • I had a BEGIN and END and something inbetween but it didnt matter cause error stayed the same so I left it – aldr Mar 16 '17 at 12:42
  • 1
    Yes, the [semicolon is needed](https://www.postgresql.org/docs/current/static/plpgsql-declarations.html), and it seems that whatever tool you are running this query with, it splits your query into multiple queries along semicolons (at least that's what happens, if your error message is complete, not just a truncated sample of the much bigger error message). – pozs Mar 16 '17 at 13:00
  • @pozs thank you, thats the solution, I use SQUIRREL there I get error, when I copy/paste my function in PgAdmin it works, would u like to write an answer? – aldr Mar 16 '17 at 13:03
  • @aldr well, your problem didn't go away with this, did it? :) You can re-phrase your question, tag SQUIRREL & ask how to solve this, because I'm not that familiar with SQUIRREL. – pozs Mar 16 '17 at 13:07
  • @pozs unbelievable, but yes it did solve my problem function works now as it should, ... – aldr Mar 16 '17 at 13:15

1 Answers1

0

the problem with the assignment is, that SQUIRREL is looking for a separator which is 

;

a solution to that problem can be found here

Community
  • 1
  • 1
aldr
  • 838
  • 2
  • 19
  • 33