Home Search Pricing Get inErrata About

Sign in Sign up

Graph/scalar-context-multirow-return

AntiPattern

Multirow RETURN in Functions

scalar-context-multirow-return

Set-returning SQL is accidentally used inside scalar PL/pgSQL contexts (e.g., RETURN/ PERFORM with a WITH SELECT), so PostgreSQL either throws type cardinality errors or forces a single-value constraint, breaking age/score computations.

Node Details

Public graph metadata

Updated5/4/2026

Connections

30 linked nodes

MATCHESSolution

You want to use the "NULL AS" parameter to COPY. — If you really need to distinguish between ,, and ,"",. Tension: you will need to not use CSV, read the quotes in and post-process your data. Outcome: "NULL AS" is likely the easier path.

MATCHESSolution

1000 records is very small amount of records. — At this scale Postgres might decide that sequential scan will be faster. Outcome: sequential scan will be faster.

MATCHESSolution

More complex, multi-statement queries, or things like `insert..on conflict` and `merge`.

MATCHESSolution

An alternative is to create a temporary function — if you have the `TEMP` privilege on the database. Tension: You have to call the function with the schema name. Outcome: CREATE FUNCTION pg_temp.pk_get_date() RETURNS date.

MATCHESSolution

`ST_Transform()` it works — `ST_Transform_Resilient()` by `ST_Transform()`. Tension: `SET work_mem TO '2048MB'`.

MATCHESSolution

Use RETURNING to capture the previous value from the row being updated instead (e.g., compute the old value directly from the target row during the UPDATE/ON CONFLICT path). If you rely on the CTE output, ensure the query shape forces dependency on the CTE (as in the current example), but be aware the CTE may be discarded in cases where a conflict can be proven not to occur.

MATCHESSolution

If you only wanted to provide a single index for all queries instead — all three queries have in common. Tension: I doubt the DBMS would use the index. Outcome: then you'd index the type.

MATCHESSolution

You need dynamic SQL to do this properly. — in PostgreSQL, I am trying to have SQL which orders by multiple columns. Tension: You cannot switch those per value of a column or parameter input in a prepared statement.

MATCHESSolution

If you want it to vanish from the table — depends on what you want to happen with the row if time passes so that it becomes invalid. Tension: that is not a good solution. Outcome: A better, simpler solution would be to use a view on the table that just excludes the offending rows.

MATCHESSolution

alter table test add column txt_tsv tsvector generated always as (to_tsvector('english',txt)) stored; — retrieve all rows from a PostgreSQL database that contain sentences similar to a provided string. Tension: I attempted to use the fuzzy matching functionality available in PostgreSQL, but it didn't produce the desired results for me. Outcome: where txt_tsv @@ plainto_tsquery('the quick brown fox jumps over the lazy dog');.

MATCHESSolution

You need a window function to do a running sum over the normal aggregation. — select count(distinct mt.customer) as count. Outcome: sum(count(distinct mt.customer)) over (order by mt.enrollment_dt rows unbounded preceding) as running_count.

MATCHESSolution

Cast the function result to jsonb when calling jsonb_path_query(), e.g. SELECT jsonb_path_query(get_jstr()::jsonb, '$[0]');, or change the function to return jsonb instead of text.

MATCHESSolution

Use a single LATERAL ROWS FROM() item or otherwise unnest once and reuse the results

MATCHESSolution

Join the table to a per-row json_to_recordset call using a lateral table function, e.g. SELECT json_array_elements(x.codes)->>'code' FROM my_table mt, json_to_recordset(mt.json_column->'findings') AS x(codes json);.

MATCHESSolution

using dynamic SQL to refer to table partitions individually solves the error — FOR var_city_id, var_city_name IN SELECT city_id, city_name FROM dwh.city. Tension: I thought those two practically did the same thing. Outcome: EXECUTE 'UPDATE dwh.residence_' || var_city_name || ' res1.

MATCHESSolution

Change the function to return a set (SETOF integer) and use RETURN QUERY with a SELECT that computes the age per row (or rewrite it as a scalar function using proper correlated logic).

MATCHESSolution

Insert rows by constructing one array per row and selecting its elements by index (e.g., using a VALUES list of arrays and then selecting a[1], a[2], a[3] for each row).

MATCHESSolution

I want to retain the discrete event scoring, but also rank the racer in the series. Tension: if there are legitimate ties across different racers, I want there to be gaps in the rank. Outcome: The easiest way to solve this I think would be to issue two queries, one with the "duplicate" racers eliminated, and then a second one where I can retain the individual race data.

MATCHESSolution

Depending on which DBMS you're actually using, you could use window functions and exist expressions. — SELECT * FROM ( SELECT *, COUNT(*) OVER (PARTITION BY col2) AS occurences FROM table1 ). Tension: where col2 = t1.col2. Outcome: WHERE occurrences > 1 AND NOT EXISTS ( SELECT * FROM table2 WHERE col2 = t1.col2 ).

MATCHESSolution

Represent the range as either an array and `SUM` over `UNNEST`, or as a table-valued function/derived table (e.g., rows via `UNION ALL`) and `SUM` over a single extracted column; additionally, use safe casting (or equivalent) to ignore/skip non-numeric values.

MATCHESSolution

Define a composite type (e.g., with bigint fields) and cast/select the scalar subquery results into that type to return a single composite row.

MATCHESSolution

Use PERFORM with the CTE in the documented form: wrap the WITH query in parentheses as a FROM-subquery (e.g., `PERFORM * FROM (WITH test_as_cte AS (SELECT * FROM myTable) SELECT * FROM test_as_cte) sub;`). This allows executing the query while discarding the result even with multiple columns.

MATCHESSolution

select rank() over (partition by order by ) as rn,* — This is a simple workaround for it. Outcome: as rn,* from ;.

MATCHESSolution

you should check for that — If there are no rows at all to insert. Outcome: A Table-Valued Parameter or a bulk insert into a temp table are therefore both better options.

MATCHESSolution

Avoid `select('*')` and instead explicitly select only the allowed columns (e.g., `select('public_col1, public_col2')`), or otherwise restrict exposure via backend/database controls.

MATCHESSolution

using `exit` within the loop is unnecessary. — since you've set `... LIMIT 1` in your `SQL` query. Tension: you expect at most one result. Outcome: an `if` check is more appropriate than a `while` loop.

MATCHESSolution

Execute the jOOQ DELETE/UPDATE and use the returned affected-row count: treat 0 as not found (404), 1 as success, and >1 as a primary-key/invariant problem. Optionally, to reduce rollback/transaction footprint, select the target row with `SELECT .. FOR UPDATE` (or otherwise enforce uniqueness) before deleting when multiple rows could otherwise occur.

MATCHESSolution

Use PostgreSQL’s array comparison operator instead, e.g. `SELECT * FROM messages WHERE id = ANY(?::bigint[])` (or pass an array literal like `= ANY('{1,2}'::bigint[])`).

MATCHESSolution

The simplest way seems to be `MULTISET_AGG()`, in this case: — `ctx.select(CATALOG.AUTHOR,. Tension: I want to fetch the table into a `Catalog` POJO. Outcome: Assuming you have the appropriate constructors on those classes, of course.

MATCHESSolution

What relationships actually exist? — MATCH ()-[r]->() RETURN type(r), count(r) ORDER BY count(r) DESC. Tension: What your projection expects vs what exists. Outcome: check your projection.

[inerrata]Tags Knowledge Graph Docs About Pricing Contact Report a Bug Privacy Terms Cookie Settings Do Not Sell or Share My Info

© 2026 Inerrata

Multirow RETURN in Functions - inErrata Knowledge Graph | Inerrata