Thread: Vectors in Pl/PgSQL
I am trying to build a join condition to join to tables with identical schemas on their unique index attributes.
So, I run a query that returns me the int2vector values from pg_index, which contains column indexes on which unique index is built. There can be multiple unique indexes built on each table, so I open a cursor and got through each int2vector returned by the query.
Now, I need to traverse through this int2vector and build the join condition. The problem is to acces elements in each int2vector withing pgsql. When I try the following, the program crashes at run-time saying that '[' is not allowed.
The cursor is called uind_curs and int2vector column is called indkey.
-- fetch next vector
FETCH uind_curs INTO rec;
EXIT WHEN NOT FOUND;
i := 0;
LOOP
EXIT WHEN rec.indkey[i] IS NULL;
RAISE NOTICE ''uind col: %'', rec.indkey[i];
i := i + 1;
END LOOP;
FETCH uind_curs INTO rec;
EXIT WHEN NOT FOUND;
i := 0;
LOOP
EXIT WHEN rec.indkey[i] IS NULL;
RAISE NOTICE ''uind col: %'', rec.indkey[i];
i := i + 1;
END LOOP;
I couldn't find any documentation on using vectors in pl/pgsql.
Any help would be greately appreciated!
Thanks.
Oleg
*************************************
This email may contain privileged or confidential material intended for the named recipient only.
If you are not the named recipient, delete this message and all attachments.
Any review, copying, printing, disclosure or other use is prohibited.
We reserve the right to monitor email sent through our network.
*************************************
Oleg Lebedev <oleg.lebedev@waterford.org> writes:
> EXIT WHEN rec.indkey[i] IS NULL;
You'll need to drop out of the loop on either zero or null, I think.
> RAISE NOTICE ''uind col: %'', rec.indkey[i];
IIRC, the RAISE statement is very very restrictive about what it will
take. The syntax error is probably coming from here.
regards, tom lane