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