I've a Postgresql function that will get some input (query value from these parameters by a dynamic query) and return the value (in here it is unique so the result should be an integer).
However, I can query the dynamic normally (it is easy just select value from table where id = 'abcd') by terminal and get value (for example: 2). But when I execute the function, the value of Executed dynamic query always return NULL.
How can I solve this? It is tired when I could not know why EXECUTE "query" INTO variable but variable return NULL while "query" can return value when run in another terminal.
Here is my function, and I want return _result_value (it always NULL - NOTICE: RESULT ABC: ). when I can query by psql.
SELECT uom_id FROM ps_quantity WHERE id = 15
uom_id
--------
1
(1 row)
CREATE OR REPLACE FUNCTION select_field(
selected_table text,
selected_field text,
field_type_sample anyelement,
where_clause text DEFAULT ''::text)
RETURNS anyelement AS
$BODY$DECLARE
-- Log
ME constant text := 'selected_field()';
-- Local variables
_qry text;
_result_value ALIAS FOR $0;
abc integer := 0;
BEGIN
RAISE NOTICE 'FUNCTION select_field';
--- _qry := 'SELECT ' || quote_ident(selected_field) ||
--- ' FROM ' || quote_ident(selected_table) ||
--- ' ' || where_clause;
_qry := ' SELECT uom_id FROM ps_quantity WHERE id = 15; ';
RAISE NOTICE 'WHAT IN HERE: query %', _qry;
-- return 1000;
-- RAISE DEBUG '%: %', ME, _qry;
RAISE NOTICE 'Preparing to query data';
-- EXECUTE _qry INTO _result_value;
--EXECUTE (' SELECT uom_id FROM ps_quantity WHERE id = ''15'' ') into abc;
EXECUTE ' SELECT uom_id FROM ps_quantity WHERE id = 15; ' into _result_value;
RAISE NOTICE 'RESULT ABC: %', _result_value;
RETURN _result_value;
END;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
