-- vim:set filetype=pgsql:
SET client_min_messages TO "NOTICE";

CREATE TABLE example1 (
	"FieldName1"                 INTEGER PRIMARY KEY
);
INSERT INTO example1 ("FieldName1") VALUES ( 1 );

-- -------------------------------------------------------------------------
CREATE FUNCTION get_field_from_cursor( field TEXT, curs REFCURSOR )
RETURNS INTEGER AS $body$
DECLARE
	rec	RECORD;
	res	INTEGER;
BEGIN
	RAISE NOTICE 'Getting field %', field;
	FETCH curs INTO rec;
	--res := rec.field;	-- ERROR:  record "rec" has no field "field"
	--res := (rec.field);	-- ERROR:  record "rec" has no field "field"
	--res := (rec).field;   -- ERROR:  syntax error at or near "$2"
	--res := rec.(field);   -- ERROR:  syntax error at or near "("
	--res := rec."FieldName1"; -- gets field, but isn't what is needed
	--SELECT INTO res field FROM rec; -- ERROR:  syntax error at or near "$2"
	return res;
END;
$body$ LANGUAGE PLPGSQL;

-- -------------------------------------------------------------------------
CREATE FUNCTION test()
RETURNS VOID AS $body$
DECLARE
	curs	REFCURSOR;
	result	INTEGER;
BEGIN
	OPEN curs FOR SELECT * FROM example1;
	result := get_field_from_cursor( 'FieldName1', curs );
	RAISE NOTICE 'Got field %', result;
END;
$body$ LANGUAGE PLPGSQL;

-- -------------------------------------------------------------------------
GRANT SELECT ON example1 TO PUBLIC;

SELECT test();

-- -------------------------------------------------------------------------
DROP TABLE example1;
DROP FUNCTION test();
DROP FUNCTION get_field_from_cursor( TEXT, REFCURSOR);
