Horton
Menu

Re: Row pattern recognition

Zsolt Parragi <zsolt.parragi@percona.com>

View thread
From: Zsolt Parragi <zsolt.parragi@percona.com>
To: Tatsuo Ishii <ishii@postgresql.org>
Cc: assam258@gmail.com, vik@postgresfriends.org, er@xs4all.nl, jacob.champion@enterprisedb.com, david.g.johnston@gmail.com, peter@eisentraut.org, pgsql-hackers@postgresql.org
Date: 2026-03-11T22:39:58Z
Lists: pgsql-hackers 
Hello

+ /*
+ * Make sure that the row pattern definition search condition is a boolean
+ * expression.
+ */
+ foreach_ptr(TargetEntry, te, defineClause)
+ (void) coerce_to_boolean(pstate, (Node *) te->expr, "DEFINE");

Isn't this incorrect? I think it should update te->expr, as currently
it is possible to construct queries where this produces unexpected
results.

CREATE TYPE truthyint AS (v int);

CREATE FUNCTION truthyint_to_bool(truthyint) RETURNS boolean AS $$
  SELECT ($1).v <> 0;
$$ LANGUAGE SQL IMMUTABLE STRICT;

CREATE CAST (truthyint AS boolean)
  WITH FUNCTION truthyint_to_bool(truthyint)
  AS ASSIGNMENT;

CREATE TABLE test_coerce (id serial, val truthyint);
INSERT INTO test_coerce VALUES
  (1, ROW(1)),
  (2, ROW(0)),
  (3, ROW(5)),
  (4, ROW(0));

SELECT id, val, COUNT(*) OVER w AS cnt
FROM test_coerce
WINDOW w AS (
    ORDER BY id
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    PATTERN (A+)
    DEFINE A AS val
)
ORDER BY id;

Same query provides the correct result with a table that has an actual
boolean column.

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Adjust cross-version upgrade tests for seg_out() fix

  2. Rationalize error comments in partition split/merge tests

  3. Add fast path for foreign key constraint checks

  4. Fix assorted pretty-trivial memory leaks in the backend.

  5. Add temporal FOREIGN KEY contraints

  6. Add trailing commas to enum definitions

  7. Remove obsolete executor cleanup code