vendredi 8 mai 2015

PostgreSQL Function Returning Table or SETOF

Quick background. Very new to PostgreSQL. Came from MS SQL Server. I am working on converting stored procedures from MS SQL Server to PostgreSQL. I have read Postgres 9.3 documentation and looked through numerous examples and questions but still cannot find a solution.

I have this select statement that I execute weekly to return new values

select distinct id, null as charges
              , generaldescription as chargedescription, amount as paidamount
from  mytable
where id not in (select id from myothertable)

What can I do to turn this into a function where I can just right click and execute on a weekly basis. Eventually I will automate this using a program another developer built. So it will execute with no user involvement and send results in a spreadsheet to the user. Not sure if that last part matters to how the function is written. Below is one of my many failed attempts:

CREATE FUNCTION newids
RETURNS TABLE (id VARCHAR, charges NUMERIC
             , chargedescription VARCHAR, paidamount NUMERIC) AS Results
Begin
SELECT DISTINCT id, NULL AS charges, generaldescription AS chargedescription, amount AS paidamount
FROM mytable
WHERE id NOT IN (SELECT id FROM myothertable)
END;
$$ LANGUAGE plpgsql;

Also I am using Navicat and the error is:

function result type must be specified

Aucun commentaire:

Enregistrer un commentaire