PostgreSQL - Combine SELECT and RETURN VALUE of a Function

1
votes

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.

2
sqlpostgresqlfunctionselect

2 Answers

0
votes

What about:

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

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

 FROM
 "Datapoint"
0
votes

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";