PostgreSQL Function Returning Table or SETOF

Question

Quick background: very new to PostgreSQL, came from SQL Server. I am working on converting stored procedures from 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.

This 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

Still receiving the same error when creating function with the Navicat tool. If I execute the command in a query editor window it gives me a syntax error at or near RETURNS — hbj, May 08 '15 at 15:14

score 5 · Answer 1 · edited May 23 '17 at 12:06

You can use PL/pgSQL here and it may even be the better choice.

Difference between language sql and language plpgsql in PostgreSQL functions

But you need to fix a syntax error, match your dollar-quoting, add an explicit cast (NULL::numeric) and use RETURN QUERY:

CREATE FUNCTION newids_plpgsql()
  RETURNS TABLE (
     id         varchar
   , charges    numeric
   , chargedescription varchar
   , paidamount numeric
   ) AS  -- Results  -- remove this
$func$
BEGIN

RETURN QUERY
SELECT ...;

END;
$func$ LANGUAGE plpgsql;

Or use a simple SQL function:

CREATE FUNCTION newids_sql()
  RETURNS TABLE (
     id         varchar
   , charges    numeric
   , chargedescription varchar
   , paidamount numeric
   ) AS
$func$
SELECT ...;
$func$ LANGUAGE sql;

Either way, the SELECT statement can be more efficient:

SELECT DISTINCT t.id, NULL::numeric AS charges
     , t.generaldescription AS chargedescription, t.amount AS paidamount
FROM   mytable t
LEFT   JOIN myothertable m ON m.id = t.id
WHERE  m.id IS NULL;

Select rows which are not present in other table

Of course, all data types must match. I can't tell, table definition is missing.
And are you sure you need DISTINCT?

Thank you. Lots of useful information. Sometimes we have duplicate rows in that table so I keep Distinct to weed them out. — hbj, May 08 '15 at 17:15

PostgreSQL Function Returning Table or SETOF

1 Answers1