PostgreSQL - Combine SELECT and RETURN VALUE of a Function

Question

In my database I have a table "Datapoint" with the two columns "Id" (integer) and "Description" (character varying). Table "Datapoint"

I then have a table "Logging" with the three columns "Id" (integer), "Dt" (timestamp without timezone) and "Value" (double precision).Table "Logging"

I also have the following function:

CREATE OR REPLACE FUNCTION count_estimate(query text)
  RETURNS integer AS
$BODY$ DECLARE rec   record;ROWS  INTEGER;BEGIN FOR rec IN EXECUTE 'EXPLAIN ' || query LOOP ROWS := SUBSTRING(rec."QUERY PLAN" FROM ' rows=([[:digit:]]+)');EXIT WHEN ROWS IS NOT NULL;END LOOP;RETURN ROWS;END $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

This function returns the estimated count of entries that are found by a SELECT-Query, e.g. SELECT count_estimate('SELECT * FROM "Logging" WHERE "Id" = 3') would return 2.

I would now like to combine a SELECT-query on the table "Datapoint" with the return value of my function, so that my result looks like this:

ID  |   Description |   EstimatedCount
1   |   Datapoint 1 |   3
2   |   Datapoint 2 |   4
3   |   Datapoint 3 |   2
4   |   Datapoint 4 |   1

My SELECT-query should look something like this:

SELECT
 "Datapoint"."Id",
 "Datapoint"."Description",

(SELECT count_estimate ('SELECT * FROM "Logging" WHERE "Logging"."Id" = "Datapoint"."Id"')) AS "EstimatedCount"

 FROM
 "Datapoint"

So my problem is to write a functioning SELECT-query for my purposes.

Chris Travers · Accepted Answer · 2016-07-04 09:00:28Z

0

What about:

SELECT
 "Datapoint"."Id",
 "Datapoint"."Description",

count_estimate ('SELECT * FROM "Logging" WHERE "Logging"."Id" = "Datapoint"."Id"') AS "EstimatedCount"

 FROM
 "Datapoint"

answered Jul 4, 2016 at 9:00

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Laurenz Albe Over a year ago

... but that's just a literal copy of the query that doesn't work, no?

Chris Travers Over a year ago

see Laurenz's fix below, but the difference here is that you don't need to put it in a subselect.

Laurenz Albe Over a year ago

Ah, I didn't see that.

Laurenz Albe · Accepted Answer · 2016-07-04 14:28:03Z

0

You almost got it right, except that you need to supply the value of "Datapoint"."Id":

SELECT
   "Datapoint"."Id",
   "Datapoint"."Description",
   count_estimate(
      'SELECT * FROM "Logging" WHERE "Logging"."Id" = ' || "Datapoint"."Id"
   ) AS "EstimatedCount"
FROM "Datapoint";

edited Jul 4, 2016 at 14:28

answered Jul 4, 2016 at 9:08

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - Combine SELECT and RETURN VALUE of a Function

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related