[GENERAL] syntax error on: GET DIAGNOSTICS xcount = ROW_COUNT;

Hi All, I am getting an error on "GET DIAGNOSTICS " statement in a PL/SQL function below. CREATE OR REPLACE FUNCTION test(lid int, OUT nid int) AS $$ DECLARE road_row road%ROWTYPE; BEGIN SELECT * INTO road_row FROM road WHERE link_id=lid; GET DIAGNOSTICS xcount = ROW_COUNT; IF xcount = 0

[GENERAL] ST_Intersects vs. ST_Touches

What are the differes between ST_Intersects and ST_Touches? The 1st query return something, but the 2nd return 0. select road.link_id from road where Intersects ( Buffer(GeometryFromText('POINT(-88.02083 41.956982)',4326), .001), the_geom ) select road.link_id from road where Touches ( Bu

[GENERAL] SCROLL CURSOR

I declare a cursor as below: DECLARE links_cur SCROLL CURSOR IS SELECT * FROM road; and get this error: CONTEXT: invalid type name "SCROLL CURSOR IS SELECT * FROM road" If I take away 'SCROLL', it has no problem. Any hint? Thanks, cyw -- Sent via p

[GENERAL] PL/PGSQL CURSOR looping/iteration - PG 8.3

I see some proposal for implementing FOR loop for cursor in PG 8.4, but I could not found documentaion on how to loop or iterate thru a cursor in PG 8.3 or earler versions? I guess I could do a FOR loop if I know the size of a cursor? How to find the size of a cursor? Thanks, CYW

[GENERAL] PLSQL function calling another function

SQL state: 42703 The full function code is below. Thanks, CYW FUNCTION B(IN id integer, OUT tid integer, OUT msg character varying) RETURNS record AS BODY$ DECLARE BEGIN msg := 'MSG'; tid := 100; RETURN; END; FUNCTION A(IN x integer, I

[GENERAL] Can PL/PGSQL function return multiple SETOFs

Is something like this possible? CREATE testsetof( IN toad_id integer) RETURNS SETOF road_table, SETOF int4 AS $BODY$ If yes, is this the way to do 'RETURN NEXT'? RETURN NEXT road_table_row, an_integer; Thanks CYW -- Sent via pgsql-general mailing list (pgs

[GENERAL] Tips on how to efficiently debugging PL/PGSQL

Just to seek some tips on how to efficiently debug PL/SQL. One thing that bugs me in particular is the inability to trace a SQL line number in an error message to the line in my PL/PGSQL code. Thanks, CYW -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make

[GENERAL] JDBC - Call stored function that returns user defined type

RECORD AS $BODY$. I found info on how to formulate a CallableStatement, but no info on how to process the results from the call? Or does Postgres JDBC support this type of calls? Any help is greatly appreciated. Regards, CYW

Re: [GENERAL] JDBC - Call stored function that returns user defined type

While I still don't know who to handle returns with scalars and user defined types mixes, I tested with a function that returns only a SETOF a user defined type, the behavior is the same as a normal select return. You can use ReseultSet to traverse and get each column. CYW - Ori