Postgresql – Postgres Procedure Returns Table

postgresqlquery

We have a table structure returned by this function:

RETURNS TABLE (ticker VARCHAR,
           current_recommendation INTEGER,
           previous_approved_recommendation INTEGER,
           date_appended TEXT,
           suggested_recommendation INTEGER,
           date_suggested TEXT,
           suggested_by VARCHAR,
           approved_recommendation INTEGER,
           date_accepted TEXT,
           approved_by VARCHAR,
           recommendation_status INTEGER
           )

On certain conditions, rather than returning values, we want to return just the first & last values, and everything else is NULL.

v_select := 'SELECT ''' || p_ticker || ''',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,' || v_status;

But it is not working:

ERROR:  structure of query does not match function result type
DETAIL:  Returned type text does not match expected type character varying in column 1.

Any help would be great. We are all kind of new at Postgres, after migrating some thing from ORacle.

Best Answer

I suppose you can solve it by using an explicit cast.

CREATE FUNCTION test(tic text)
RETURNS TABLE (foo VARCHAR, bar int)
AS $$
DECLARE
  sql text;
BEGIN
  sql := 'SELECT ''' || tic || ''', NULL';
  RETURN QUERY EXECUTE sql;
END
$$
LANGUAGE plpgsql;

SELECT test('A');

ERROR: structure of query does not match function result type DETAIL: Returned type text does not match expected type character varying in column 1.

CREATE FUNCTION test1(tic text)
RETURNS TABLE (foo VARCHAR, bar int)
AS $$
DECLARE
  sql text;
BEGIN
  sql := 'SELECT ''' || tic || '''::VARCHAR, NULL::int';
  RETURN QUERY EXECUTE sql;
END
$$
LANGUAGE plpgsql;

SELECT test1('A');

| test1 |
| :---- |
| (A,)  |

db<>fiddle here

`DISTINCT ON()`

Just as a side note, this is precisely what DISTINCT ON() does (not to be confused with DISTINCT)

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example

So if you were to write,

SELECT myFirstAgg(z)
FROM foo
GROUP BY x,y;

It's effectively

SELECT DISTINCT ON(x,y) z
FROM foo;
-- ORDER BY z;

In that it takes the first z. There are two important differences,

You can also select other columns at no cost of further aggregation..

SELECT DISTINCT ON(x,y) z, k, r, t, v
FROM foo;
-- ORDER BY z, k, r, t, v;

Because there is no GROUP BY you can not use (real) aggregates with it.

CREATE TABLE foo AS
SELECT * FROM ( VALUES
  (1,2,3),
  (1,2,4),
  (1,2,5)
) AS t(x,y,z);

SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- fails, as you should expect.
SELECT DISTINCT ON (x,y) z, sum(z)
FROM foo;

-- would not otherwise fail.
SELECT myFirstAgg(z), sum(z)
FROM foo
GROUP BY x,y;

Don't forget `ORDER BY`

Also, while I didn't bold it then I will now

Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example

Always use an ORDER BY with DISTINCT ON

Using an Ordered-Set Aggregate Function

I imagine a lot of people are looking for first_value, Ordered-Set Aggregate Functions. Just wanted to throw that out there. It would look like this, if the function existed:

SELECT a, b, first_value() WITHIN GROUP (ORDER BY z)    
FROM foo
GROUP BY a,b;

But, alas you can do this.

SELECT a, b, percentile_disc(0) WITHIN GROUP (ORDER BY z)   
FROM foo
GROUP BY a,b;

Postgresql – Improve performance on concurrent UPDATEs for a timestamp column in Postgres

Would a different kind of column be faster? For example an integer

No. timestamp and timestamptz are just unsigned 64-bit integers internally anyway.

Is there some way to not lock the column?

It doesn't lock the column. It takes weak table lock that doesn't really block anything except DDL, and takes a row level lock on the row you're updating.

There is no way to prevent the row level lock. It exists because without it behaviour and ordering concurrent updates would be undefined. We don't like undefined behaviour in RDBMSs.

It only blocks concurrent updates of the same row anyway.

Any other tips to improve this?

Not with the detail provided. There's likely a better way to do what you're trying to do, but it'll probably involve taking a few steps back and looking for a different strategy for solving the underlying problem.

In the specific case of cache invalidation I think you might want to look into LISTEN and NOTIFY. Again though, there just isn't enough info here to go on.

Best Answer

Related Solutions

Postgresql – In PostgreSQL, is there a type-safe first() aggregate function

DISTINCT ON()

Don't forget ORDER BY

Using an Ordered-Set Aggregate Function

Postgresql – Improve performance on concurrent UPDATEs for a timestamp column in Postgres

Related Question

`DISTINCT ON()`

Don't forget `ORDER BY`