My postgresql function created with HeidiSQL is not returning results

Question

v_all_my_tables contains a single column (from charvar) which I'd like to loop through and run a select query on.

HeidiSQL creates the function using the code below. However, when I run routine, no results are returned and this error is logged

SELECT "myFunc"();
/* Unknown datatype oid #2278 for "myFunc". Fall back to UNKNOWN.

I'm new to postgres so I'm probably missing something obvious here. Thanks!

DELIMITER //
CREATE OR REPLACE FUNCTION myFunc() 
  RETURNS void AS
$func$
DECLARE
   _tbl text;
BEGIN
   FOR _tbl IN
      SELECT table_name FROM v_all_my_tables ORDER BY TABLE_NAME ASC
   LOOP
      EXECUTE
      format('SELECT distinct lastupdate FROM %I order by lastupdate DESC LIMIT 1', _tbl); 
   END LOOP;
END
$func$  LANGUAGE plpgsql;

score 0 · Accepted Answer · answered Jun 18 '20 at 12:41

You need to call EXECUTE with INTO clause to store result of executed SQL statement. You may need to use RETURN to return some result. You should not use double quotes when calling function if double quotes have not been used when defining the function (and in general you don't need double quotes for identifier in PostgreSQL).

Example:

select * from t1;
 lastupdate 
------------
 2020-06-18
(1 row)

select * from v_all_my_tables;
 table_name 
------------
 t1
(1 row)


CREATE OR REPLACE FUNCTION myFunc() 
 RETURNS date AS
$func$
DECLARE
   _tbl text;
   v_lastupdate date;
BEGIN
   FOR _tbl IN
      SELECT table_name FROM v_all_my_tables ORDER BY TABLE_NAME ASC
   LOOP
      EXECUTE
       format('SELECT distinct lastupdate FROM %I order by lastupdate DESC LIMIT 1', _tbl)
       INTO v_lastupdate;
   END LOOP;
   RETURN v_lastupdate;
END
$func$  LANGUAGE plpgsql;
CREATE FUNCTION

select myFunc();
   myfunc   
------------
 2020-06-18
(1 row)

My postgresql function created with HeidiSQL is not returning results

1 Answers1