CODEWITHSUNDEEP
sqlpostgresqlfunction
Kazei Itsushi
Kazei Itsushi

Reputation: 19

How to return in separated column using PostgreSQL Function()

Here is my script, once i run the script, it return in single colomn with value like (val1,val2,val3,bla..bla bla...

CREATE OR REPLACE FUNCTION meter_latest_read_custom()
RETURNS TABLE(
  maxdate timestamp without time zone, 
  ertu integer, 
  meter integer, 
  meter_name character varying, 
  acq_9010 numeric) 
AS
$BODY$
DECLARE
  formal_table text;
BEGIN
  FOR formal_table IN
    SELECT 
      quote_ident(table_name)
    FROM
      information_schema.tables
    WHERE  
      table_schema = 'public' AND
      table_name LIKE 'task%_1'
   LOOP
      RETURN QUERY EXECUTE
      'with groupedft as (
        SELECT meter_id, MAX(acq_date) AS MaxDateTime
          FROM ' ||formal_table|| ' GROUP BY meter_id),
      foo as (
        SELECT 
          ft.acq_date AS maxdate, 
          ft.ertu_id AS ertu, 
          ft.meter_id AS meter, 
          ft.acq_9010 AS acq_9010
        FROM 
          '||formal_table|| ' ft
        INNER JOIN groupedft 
        ON 
          ft.meter_id = groupedft.meter_id 
          AND ft.acq_date = groupedft.MaxDateTime)
      SELECT 
        maxdate, ertu, meter, m.meter_name, acq_9010
      FROM
        foo
      LEFT JOIN 
        meter_record m 
      ON 
        foo.meter=m.meter_id 
        AND foo.ertu=m.ertu_id';

   END LOOP;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION meter_latest_read_custom() OWNER TO postgres;

The result will be return in single column

"("2017-02-16 10:45:00",201,6,"SPARE 6",)"
"("2017-02-16 10:45:00",201,14,"SPARE 14",)"
"("2017-02-16 10:45:00",201,8,"SPARE 8",)"
"("2017-02-16 10:45:00",201,12,"SPARE 12",)"
"("2017-02-16 10:45:00",201,1,"E.CO-PUAS KAJANG/AC PANEL ETS",16986.00000)"
"("2017-02-16 10:45:00",201,2,"SPARE 2",)"
"("2017-02-16 10:45:00",201,3,"SPARE 3",)"
"("2017-02-16 10:45:00",201,10,"SPARE 10",)"
"("2017-02-16 10:45:00",201,11,"SPARE 11",)"
"("2017-02-16 10:45:00",201,4,"SPARE 4",)"

Im required the result return in seperated column not in one column.. where to modify it. ?

-------------------------------------------------------------------
| maxdate   |    ertu   |   meter    |   meter_name |   acq_9010  |
-------------------------------------------------------------------
|           |           |            |              |             |
|           |           |            |              |             |
|           |           |            |              |             |
|           |           |            |              |             |
|           |           |            |              |             |
|           |           |            |              |             |
|           |           |            |              |             |
|           |           |            |              |             |

Upvotes: 1

Views: 75

Answers (1)

chalitha geekiyanage
chalitha geekiyanage

Reputation: 6812

Try this way

SELECT * FROM meter_latest_read_custom();

Upvotes: 3

Related Questions