Fork me on GitHub
#sql
<
2020-02-28
>
dharrigan14:02:01

Anyone here used a psql function with a parameter that is also used inside an inner (nested) select?

ben14:02:16

I’m not sure what exactly you mean. Do you have an example?

dharrigan14:02:37

Well, the question is more leading 🙂 If I define a function with a paramter called foo, I can't seem to refer to that parameter within a nested select. On the outer select, yes, no problem, I can see it, but not within the inner 🙂

dharrigan15:02:32

Here's a very convoluted example

dharrigan15:02:33

CREATE OR REPLACE FUNCTION foobar(foo INTEGER DEFAULT 1)
AS
$$
BEGIN
    RETURN QUERY
        SELECT a.b
        FROM (SELECT a.c FROM a WHERE a.datetime BETWEEN current_date - INTERVAL foo AND current_date) a;
        --WHERE foo = 1;
END;
$$
    LANGUAGE plpgsql;

dharrigan15:02:04

the variable foo cannot be accessed within the inner select, yet it can be accessed in the where clause

dharrigan15:02:48

The query isn't functional (ha!), it's mearly to illustrate the issue that I face 🙂

ben15:02:34

I see. I’ve no idea I’m afraid. Is there any chance using a CTE instead of a nested select would help?

dharrigan15:02:41

Do'h fixed. I was using the wrong datatype

dharrigan15:02:50

so naturally it couldn't be used

grzm19:02:33

nits: psql is the PostgreSQL command line tool. The language you're using for the function is PL/pgSQL, and the project as a whole is PostgreSQL, postgres, or pg, if you're into the whole brevity thing.

Ben Hammond17:02:58

I am looking at a database optimization that my predecessor has done that is trying to cache a count() value that runs a bit like this

INSERT INTO cached_count (id, countedval)
VALUES (1, (SELECT count(id) from biggish_table where condition IS NULL AND id = 1))
ON CONFLICT (id) DO
    UPDATE
    SET countedval = cached_count.countedval -1
RETURNING countedval;

Ben Hammond17:02:53

my objection to this is that it always runs the count(id) meaning that it doesn't optimize anything, it just creates overhead

Ben Hammond17:02:18

I only want that to run if the row for id is missing

Ben Hammond17:02:48

what I really want is an

UPDATE ... ON MISSING
construct

Ben Hammond17:02:51

or near offer

Ben Hammond17:02:02

This is in Postgres BTW

Ben Hammond17:02:15

I wonder if I can abuse COALESCE into helping...

Ben Hammond17:02:32

ooh dear that sounds ouchy

seancorfield17:02:36

(sorry, cat on keyboard)

🐈 8
Ben Hammond17:02:53

if anyone has a pet way to UPDATE ON MISSING , I am agog to hear it

Ben Hammond18:02:19

otherwise I guess I'm best off stripping out this 'optimisation` - its got a noticeable overhead but no benefit

isak18:02:59

You could use ROW_COUNT, I think: https://stackoverflow.com/a/36325731

parameme11:03:24

My understanding is that ROW_COUNT would only help if you processed / touched all the rows you are wanting to count on that cursor. This really needs some thought / design to balance the need for performance improvement and read vs write frequency (and cache invalidation). An index on biggish_table (id, <any condition columns>) could help significantly. Same index with a WHERE condition IS NULL could also help. And specific index types might help. But if it really has to be optimal in I/O though then aggregate maintenance on change really requires either app code to support or triggers to maintain.

👍 4
Ben Hammond12:03:55

yeah I'm concluding that the extra complexity makes it really not worth the trouble

seancorfield19:02:12

I just released seancorfield/next.jdbc {:mvn/version "1.0.384"} -- https://github.com/seancorfield/next-jdbc -- It's mostly documentation updates based on feedback here and via issues, but also a few minor performance tweaks for result set builders, and a bug fix to how column name keywords are constructed when the table name is not available. The latter also means you can now use :qualifier-fn (constantly "prefix") even when the table name is not available for a given column in the results, which provides an easier migration from clojure.java.jdbc.

seancorfield19:02:57

The documentation updates include examples of datafiable-row and more explanation of what it is about.

seancorfield19:02:38

Thanks to everyone here who has provided feedback on the docs (and to other folks who've provided feedback via GitHub issues)!

seancorfield19:02:24

(and, yes, just like many of the Contrib libraries, I've decided to switch to major.minor.commits for versioning)