Postgresql – Create function with a parameter, which will become the part of IN (1, 2, 3) clause

parameterplpgsqlpostgresql

Following is the part of function declaration. Unfortunately it doesn't work the way I would like it to.

CREATE FUNCTION my_cool_func(some_parameter INTEGER, some_other_parameter INTEGER[])
RETURNS BOOLEAN
LANGUAGE 'plpgsql'
STABLE 

AS $BODY$

DECLARE count_entries INTEGER;

BEGIN

    SELECT COUNT(some_table.id) INTO count_entries FROM some_table
    WHERE ...
    AND some_table.some_integer_field IN (some_other_parameter);

        IF (count_entries > 0) THEN...

some_other_parameter is a list of integer values (1, 2, 3).

My questions:

Is this function declaration correct?
How do I define the default value for some_other_parameter?

Best Answer

Use = ANY instead of IN

AND some_table.some_integer_field = ANY (some_other_parameter);

The equivalent of NOT IN would be <> ALL (...)

To declare a parameter with a default value use:

CREATE FUNCTION my_cool_func(..., p_id_list INTEGER[] default array[42])

Related Solutions

PostgreSQL Naming Conflict – Function Parameter vs JOIN USING Clause

According to the docs PL/pgSQL Under the Hood, you can use the configuration parameter plpgsql.variable_conflict, either before creating the function or at the start of the function definition, declaring how you want such conflicts to be resolved.
The 3 possible settings are error (the default), use_variable and use_column:

CREATE OR REPLACE FUNCTION f_merge_foobar()
  RETURNS TABLE(ts int, foo text, bar text)
  LANGUAGE plpgsql AS
$func$
#variable_conflict use_column             -- how to resolve conflicts
BEGIN
   FOR ts, foo, bar IN
      SELECT ts, f.foo, b.bar
      FROM   foo f
      FULL   JOIN bar b USING (ts)
   LOOP
      -- do something
      RETURN NEXT;
   END LOOP;
END
$func$;

PostgreSQL – Index Not Being Used in SELECT Query

According to your current explanation indexes are not going to help much (if at all) with your current query.

So there are 3.25M rows with a project key.

That's also the total number of rows, so this predicate is true for (almost) every row ... and not selective at all. But there is no useful index for the jsonb column "references". Including it in the btree index on ("createdAt", "references", type) is just pointless.

Even if you had a generally more useful GIN index on "reference" like:

CREATE INDEX stats_references_gix ON stats USING gin ("references");

... Postgres would still have no useful statistics about individual keys inside the jsonb column.

There are only 5 distinct values for the type

Your query selects all of one type and an unknown fraction of another type. That's an estimated 20 - 40 % of all rows. A sequential scan is most certainly going to be fastest plan. Indexes start to make sense for around 5 % of all rows or less.

To test, you can force a possible index by setting for debugging purposes in your session:

SET enable_seqscan = off;

Reset with:

RESET enable_seqscan;

You'll see slower queries ...

You group by project values:

GROUP BY "references"->> 'project'

And:

There are exactly 400,000 distinct values for the project reference.

That's 8 rows per project on average. Depending on value frequencies we still have to retrieve an estimated 3 - 20 % of all rows if we only picked min and max per project in a LATERAL subquery ...

Try this index, it makes more sense than what you have now:

CREATE INDEX stats_special_idx ON stats (type, ("references" ->> 'project'), "createdAt")
WHERE "references" ? 'project';

Postgres might still fall back to a sequential scan ...

More might be done with a normalized schema / more selective criteria / a smarter query that only picks min and max "createdAt" ...

Query

I would write your query like this:

SELECT EXTRACT(EPOCH FROM (MAX("createdAt") - MIN("createdAt"))) 
FROM   stats
WHERE  "references" ? 'project'
AND   (type = 'event1' OR
       type = 'event2'
   AND "createdAt" >= '2015-11-02 00:00:00+08:00'  -- I guess you want this
   AND "createdAt" <  '2015-12-04 00:00:00+08:00'
      )
GROUP  BY "references"->> 'project';  -- don't cast

Notes

Don't cast here:

stats."references"::jsonb ? 'project'

The column is jsonb already, you gain nothing. If the predicate was selective, index usage might be prohibited by the cast.
Your predicates on "createdAt" are probably incorrect at lower and upper bound. To include whole days consider my suggested alternative.
references is a reserved word, so you have to always double-quote it. Don't use it as identifier. Similar for double-quoted CaMeL-case names like "createdAt" either. Allowed, but error prone, needless complication.
type

type character varying(255) NOT NULL,

The type is a simple string no longer than 50 characters.

There are only 5 distinct values for the type field, this would probably be no more than a few hundred in production.

None of this seems to make sense.
- varchar(255) in itself hardly ever makes any sense. 255 characters is an arbitrary limit without significance in Postgres.
- If it's no longer than 50 chars, then the limit of 255 makes even less sense.
- In a properly normalized design you would have a small integer column type_id (referencing a small type table) which occupies only 4 bytes per row and makes indexes smaller and faster.
Ideally, you would have a project table, listing all projects and another small integer FK column project_id in stats. Would make any such query faster. And for selective criteria, much faster queries would be possible - even without the suggested normalization. Along these lines:
Optimize GROUP BY query to retrieve latest record per user