Postgres + Sequelize: How to read function result?

Question

I have a function payment_summary() as below:

CREATE OR REPLACE FUNCTION payment_summary()
  RETURNS SETOF PAYMENT_SUMMARY_TYPE
LANGUAGE plpgsql
AS $$
DECLARE
  payment_sum payment_summary_type%ROWTYPE;
BEGIN
  FOR payment_sum IN SELECT
                       pay.application_no,
                       project.title,
                       pay.payment_rec,
                       customer.cust_name,
                       project.estimated_cost,
                       (project.estimated_cost - pay.payment_rec) AS outstanding_amt
                     FROM project
                       INNER JOIN customer
                         ON project.customer_cust_id = customer.cust_id
                       INNER JOIN
                       (SELECT
                          project.application_no,
                          sum(payment.amount) AS payment_rec
                        FROM payment
                          INNER JOIN project
                            ON payment.project_id = project.project_id
                        WHERE payment.drcr_flg = 'Cr'
                        GROUP BY project.application_no) AS pay
                         ON pay.application_no = project.application_no
  LOOP
    RETURN NEXT payment_sum;
  END LOOP;
END;
$$;

PAYMENT_SUMMARY_TYPE is defined as:

CREATE TYPE PAYMENT_SUMMARY_TYPE AS
(
  application_no  VARCHAR(150),
  title           VARCHAR(500),
  payment_rec     INTEGER,
  customer_name   VARCHAR(500),
  estimated_cost  INTEGER,
  outstanding_amt INTEGER
);

Using below code to execute the function and get results:

sequelize.query('SELECT payment_summary()').then(function(data) {
        res.json(data);
 });

Getting below as response:

[
    [
        {
            "payment_summary": "(716,\"C1\",100000,\"C1 - city\",0,-100000)"
        },
        {
            "payment_summary": "(716,\"C2\",100000,\"C2 - city\",0,-100000)"
        }
    ],
    {
        "command": "SELECT",
        "rowCount": 2,
        "oid": null,
        "rows": [
        {
            "payment_summary": "(716,\"C1\",100000,\"C1 - city\",0,-100000)"
        },
        {
            "payment_summary": "(716,\"C2\",100000,\"C2 - city\",0,-100000)"
        }
        ],
        "fields": [
            {
                "name": "payment_summary",
                "tableID": 0,
                "columnID": 0,
                "dataTypeID": 17453,
                "dataTypeSize": -1,
                "dataTypeModifier": -1,
                "format": "text"
            }
        ],
        "_parsers": [
            null
        ],
        "rowAsArray": false
    }
]

I need the response in below format:

[
    {
        application_no: 716,
        title: "C1",
        payment_rec : 100000,    
        customer_name : "C1 - city"
        estimated_cost : 0 
        outstanding_amt : -100000
    },
    {
        application_no: 717,
        title: "C2",
        payment_rec : 100000,    
        customer_name : "C2 - city"
        estimated_cost : 0 
        outstanding_amt : -100000
    }
]

How can i read / convert the response in required format ?

Answer 1

I hope this could help someone else, I just run into the same problem, the difference between just SELECT function_name() and SELECT * FROM function_name(), you can find it here What is the difference between select from function and select function

You can change your current query to this:

sequelize.query('SELECT * FROM payment_summary()').then(function(data) {
    res.json(data);
});