store function output in an array

Question

I have the follwoing function which works fine, BUT I am not sure if it is storing anything in the array.

DECLARE 
     _r record;
     point character varying[] := '{}';
     i int := 0;

BEGIN



FOR _r IN EXECUTE ' SELECT a.'|| quote_ident(column_name) || ' AS point
       FROM ' || quote_ident (table_name) ||' AS a'
LOOP

       point[i] = _r;
       i = i+1;

END LOOP;

RETURN point;
END;

What I am after is to have an array that from the javascript side i can traverse and read each value stored on the array. Is it the right way of doing it?

Correct your tags. How the question relates with sqlserver and postgis? — Hamlet Hakobyan, Feb 19 '13 at 10:28
since I am using spatial data as the column data which each row holds a point value @Hamlet Hakobyan. — IT_info, Feb 19 '13 at 10:30
I think Correct tags means, `correctly tag your question`. What *db server* your question related to? — Kaf, Feb 19 '13 at 12:38
What error do you get? Have you tried to print `point` value via `RAISE`? — vyegorov, Feb 19 '13 at 12:49
What does it really RAISE.? I am stull new to plpgsql @vyegorov — IT_info, Feb 19 '13 at 13:08

score 1 · Answer 1 · answered Feb 19 '13 at 13:24

Arrays in PL/pgSQL or PostgreSQL has nothing to do with arrays in javascript or php.

If you're working with libpq-aware client, like php, then any query to the databases will return a set of records, which can be accumulated into the internal array variable of the calling party, like pg_fetch_array() of php.

If you're working with thin clients, like javascript is, you will need to return data in the format suitable for javascript to understand. You will need to have a server-side data providing service, that will obtain results from the PostgreSQL via libpq native interface and convert it to the javascript-ready one, like JSON. PostgreSQL has native support for the XML data type and also JSON (a bit limited at the moment). Not sure if that will help you much though.

As to the PL/pgSQL functions, I really recommend reading official docs on this language. In order to see the value of point at runtime, use this statement:

RAISE NOTICE 'point = %', point;

store function output in an array

1 Answers1