Обсуждение: You might be able to move the set-returning function into a LATERALFROM item.
You might be able to move the set-returning function into a LATERALFROM item.
От
Alexander Farber
Дата:
Good morning,
for a word game in PostgreSQL 10.3 I have a table with jsonb column "tiles".
The column holds either a JSON array of objects (word tiles played) or a string (of swapped letters).
I am trying to fetch a history/protocol of a game with:
CREATE OR REPLACE FUNCTION words_get_moves(
in_gid integer
) RETURNS TABLE (
out_action text,
out_letters text,
out_words text
) AS
$func$
WITH cte1 AS (
SELECT
mid,
action,
STRING_AGG(x->>'letter', '') AS tiles
FROM (
SELECT
mid,
action,
CASE WHEN JSONB_TYPEOF(tiles) = 'array' THEN JSONB_ARRAY_ELEMENTS(tiles) ELSE NULL END AS x
--JSONB_ARRAY_ELEMENTS(tiles) AS x
FROM words_moves
WHERE gid = in_gid
--AND JSONB_TYPEOF(tiles) = 'array'
) AS p
GROUP BY mid, action),
cte2 AS (
SELECT
mid,
STRING_AGG(y, ', ') AS words
FROM (
SELECT
mid,
FORMAT('%s (%s)', word, score) AS y
FROM words_scores
WHERE gid = in_gid
) AS q
GROUP BY mid)
SELECT
action,
tiles,
words
FROM cte1
LEFT JOIN cte2 using (mid)
ORDER BY mid ASC;
$func$ LANGUAGE sql;
However calling this stored function gives the error:
ERROR: 0A000: set-returning functions are not allowed in CASE
LINE 18: ... CASE WHEN JSONB_TYPEOF(tiles) = 'array' THEN JSONB_ARRA...
^
HINT: You might be able to move the set-returning function into a LATERAL FROM item.
I have read that PostgreSQL 10 handles SRF more strictly, but what does it want me to do here, to add 1 more table to the LEFT JOIN?
Thank you
Alex
Re: You might be able to move the set-returning function into aLATERAL FROM item.
От
Laurenz Albe
Дата:
Alexander Farber wrote: > for a word game in PostgreSQL 10.3 I have a table with jsonb column "tiles". > > The column holds either a JSON array of objects (word tiles played) or a string (of swapped letters). > > I am trying to fetch a history/protocol of a game with: > > > CREATE OR REPLACE FUNCTION words_get_moves( > [...] AS > $func$ > [...] > CASE WHEN JSONB_TYPEOF(tiles) = 'array' THEN JSONB_ARRAY_ELEMENTS(tiles) ELSE NULL END AS x > [...] > $func$ LANGUAGE sql; > > However calling this stored function gives the error: > > ERROR: 0A000: set-returning functions are not allowed in CASE > LINE 18: ... CASE WHEN JSONB_TYPEOF(tiles) = 'array' THEN JSONB_ARRA... > ^ > HINT: You might be able to move the set-returning function into a LATERAL FROM item. > > I have read that PostgreSQL 10 handles SRF more strictly, but what does it want me to do here, to add 1 more table to theLEFT JOIN? The problem is that "jsonb_array_elements" returns several rows, which does not make sense in this context. Which of the rows do you want? If you know that it will always return at most one row, you could use: ... THEN (SELECT jae FROM jsonb_array_elements(tiles) jae LIMIT 1) Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Alexander Farber <alexander.farber@gmail.com> writes:
> I am trying to fetch a history/protocol of a game with:
> SELECT
> CASE WHEN JSONB_TYPEOF(tiles) = 'array' THEN
> JSONB_ARRAY_ELEMENTS(tiles) ELSE NULL END AS x
I think you could push the conditionality into a plpgsql function,
something like (untested)
create function jsonb_elements_if_array(j jsonb) returns setof jsonb as $$
begin
if jsonb_typeof(j) = 'array' then
return query select jsonb_array_elements(j);
end if;
end$$
strict immutable language plpgsql;
Note that this gives *no* elements, rather than a single NULL value,
if the input isn't an array --- but that seems to me to make more sense
than your existing code anyhow. If you disagree, add "else return next
null::jsonb".
regards, tom lane
Re: You might be able to move the set-returning function into aLATERAL FROM item.
От
Alexander Farber
Дата:
Thank you -
On Tue, Mar 20, 2018 at 3:27 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I think you could push the conditionality into a plpgsql function,
something like (untested)
create function jsonb_elements_if_array(j jsonb) returns setof jsonb as $$
begin
if jsonb_typeof(j) = 'array' then
return query select jsonb_array_elements(j);
end if;
end$$
strict immutable language plpgsql;
Note that this gives *no* elements, rather than a single NULL value,
if the input isn't an array --- but that seems to me to make more sense
than your existing code anyhow. If you disagree, add "else return next
null::jsonb".
I think I will just introduce a separate column (until now I was trying to squeeze 2 different kinds of data - JSON array of objects and a string - into the one column)... I believe Adrian had suggested it before :-)