Clojurians Log v2

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

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

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 🙂

Here's a very convoluted example

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;

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

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

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

Do'h fixed. I was using the wrong datatype

so naturally it couldn't be used

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;

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

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

what I really want is an

UPDATE ... ON MISSING

or near offer

This is in Postgres BTW

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

ooh dear that sounds ouchy

(sorry, cat on keyboard)

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

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

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

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.

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.

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

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

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

Wow! That was quick