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
– Hamlet Hakobyan, Commented Feb 19, 2013 at 10:28
since I am using spatial data as the column data which each row holds a point value @Hamlet Hakobyan. — IT_info
– IT_info, Commented Feb 19, 2013 at 10:30
I think Correct tags means, correctly tag your question. What db server your question related to? — Indi
– Indi, Commented Feb 19, 2013 at 12:38
What error do you get? Have you tried to print point value via RAISE? — vyegorov
– vyegorov, Commented Feb 19, 2013 at 12:49
What does it really RAISE.? I am stull new to plpgsql @vyegorov — IT_info
– IT_info, Commented Feb 19, 2013 at 13:08

vyegorov · Accepted Answer · 2013-02-19 13:24:37Z

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;

Collectives™ on Stack Overflow

store function output in an array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related