Good afternoon,
in PostgreSQL 10.3 I have the following table with a jsonb column:
# \d words_moves;
Table "public.words_moves"
Column | Type | Collation | Nullable |
Default
+--+---+--+
Hi Adrian, thank you for the reply -
On Fri, Mar 2, 2018 at 3:05 PM, Adrian Klaver
wrote:
> On 03/02/2018 05:52 AM, Alexander Farber wrote:
>
>>
>> in PostgreSQL 10.3 I have the following table with a jsonb column:
>>
>> # \d words_moves;
>>
And here is how I call my stored function -
https://gist.github.com/afarber/88a832a1b90a8940764ad69b2b761914
- why wouldn't it store a jsonb array in the tiles column of words_moves
table?
2018-03-02 15:29:42.644 CET [16693] LOG: statement: DISCARD ALL
2018-03-02 15:29:42.644 CET [16693] LOG:
Hi Adrian, I 100% agree that nobody except me should debug my huge stored
function, but if you look at my PostgreSQL 10.3 log -
On Fri, Mar 2, 2018 at 3:31 PM, Adrian Klaver
wrote:
>
> The little gray cells are not awake enough to work through the below:) If
> it where me I would first confirm t
Good evening -
On Fri, Mar 2, 2018 at 7:11 PM, Adrian Klaver
wrote:
>
>> 2018-03-02 15:30:33.646 CET [16693] LOG: duration: 0.058 ms bind
>> : SELECT out_uid AS uid, out_fcm AS fcm, out_apns AS
>> apns, out_adm AS adm, out_body AS body FROM
>> words_play_game($1::int, $2
# select * from words_moves where gid=609 limit 3;
-[ RECORD 1
]---
Oops, I've got strings there too - when swapping instead of playing tiles:
# select * from words_moves where gid=609 and action <> 'play';
mid | action | gid | uid |played | tiles |
score
--++-+--+---+--+---
I see, thank you for your comments, David and Adrian.
In the "tiles" column actually save either the JSON array of tiles - when
the user plays them
Or a string (which is jsonb too) concatenated of letters - when the user
swaps the letters.
Maybe I should rethink my table structure (I just want t
Good afternoon,
A PostgreSQL 10.3 table contains JSON data like:
[{"col": 7, "row": 12, "value": 3, "letter": "A"}, {"col": 8, "row": 12,
"value": 10, "letter": "B"}, {"col": 9, "row": 12, "value": 1, "letter":
"C"}, {"col": 10, "row": 12, "value": 2, "letter": "D"}]
Please suggest, how to extra
Thank you, Ivan! I am trying to apply your suggestion to my table -
On Wed, Mar 14, 2018 at 5:34 PM, Ivan E. Panchenko <
i.panche...@postgrespro.ru> wrote:
> Hi Alex,
>
> SELECT string_agg(x->>'letter','') FROM json_array_elements(
>
> '[{"col": 7, "row": 12, "value": 3, "letter": "A"}, {"col":
Thank you -
On Wed, Mar 14, 2018 at 8:41 PM, Ivan E. Panchenko <
i.panche...@postgrespro.ru> wrote:
> Yes, here x is the alias for the record, not for the json field. So you
> need to write the query like
>
> select string_agg(x->>'letter', ' ')
> from (
>select jsonb_array_elements(tiles) x
Good afternoon,
I have prepared an SQL Fiddle for my question:
http://sqlfiddle.com/#!17/4ef8b/2
Here are my 4 test tables:
CREATE TABLE players (
uid SERIAL PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE games (
gid SERIAL PRIMARY KEY,
player1 integer NOT NULL REFERENCES playe
Hi David -
On Fri, Mar 16, 2018 at 4:40 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> First reaction is to ARRAY_AGG(DISTINCT x) and then write a function that
> converts that array into a string by extracting 'letter' from each cell in
> the array.
>
> Thinking it over a bit you
Thank you, David -
On Fri, Mar 16, 2018 at 5:40 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Fri, Mar 16, 2018 at 9:10 AM, Alexander Farber <
> alexander.far...@gmail.com> wrote:
>
>> SELECT mid,
>>
> (SELECT string_agg(tiles, '; &
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(
Thank you -
On Tue, Mar 20, 2018 at 3:27 PM, Tom Lane 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
Good evening,
in PostgreSQL 10.3 I have written the following custom function (trying to
fetch 10 latest games played by a user):
CREATE OR REPLACE FUNCTION words_stat_games(
in_social integer,
in_sidtext
) RETURNS TABLE (
out_gidint
Ok thanks, I guess I should switch to a SELECT UNION (first on uid =
player1 and the uid = player2) and that will fix the CASE ... END for me.
On Tue, Apr 10, 2018 at 6:01 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Tue, Apr 10, 2018 at 8:44 AM, Alexand
Or actually I can not use SELECT UNION here, because then I only get 10
records of the condition uid = player1 and then nothing would be left for
the other condition uid = player2
Last night I have inexplicably missed 2 conditions /facepalm
Now my JOIN works ok, without multiple records -
CREATE OR REPLACE FUNCTION words_stat_games(
in_social integer,
in_sidtext
) RETURNS TABLE (
out_gidinteger,
Hello,
in PostgreSQL 10.3 I run the following query to find top 10 players with
the best ELO rating:
# SELECT
u.elo,
u.uid,
s.given,
s.photo
FROM words_users u
JOIN words_social s USING (uid)
WHERE u.elo > 1500
-- take the most recent record from words_social (sto
Thank you, Daniel -
On Mon, Apr 23, 2018 at 12:15 PM, Daniel Verite
wrote:
>
> You may use a correlated subquery in the SELECT clause,
> like this:
>
> SELECT
>u.elo,
>u.uid,
>(SELECT AVG(score) FROM words_moves WHERE uid=u.uid),
>s.given,
>s.photo
>
thi
Good evening,
On Mon, Apr 23, 2018 at 12:56 PM, Alexander Farber <
alexander.far...@gmail.com> wrote:
>
> On Mon, Apr 23, 2018 at 12:15 PM, Daniel Verite
> wrote:
>
>>
>> You may use a correlated subquery in the SELECT clause,
>>
>
>
Thank you for the explanation. I have rearranged my query and it works now
(surprisingly fast too) -
On Mon, Apr 23, 2018 at 9:58 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Mon, Apr 23, 2018 at 12:47 PM, Alexander Farber <
> alexander.far...@g
Good evening,
I have written a custom function which works, but wonder if using same
condition twice looks suspicious and can be optimized.
Here is calling my function, it returns average score / day and average
time between moves / day:
# select * from words_stat_scores(1, '19992844041575538327
Thank you for the insightful comments.
Actually in my specific case I have managed to get rid of the (almost) same
condition in the outer query:
CREATE OR REPLACE FUNCTION words_stat_scores_2(
in_social integer,
in_sidtext
) RETURNS TABLE (
s/ I can trust / I can't trust /
Hello,
I am struggling with an UPSERT in PostgreSQL 10.3 and have prepared a
simple test case showing my 2 problems (at
http://sqlfiddle.com/#!17/7e929/13 and also below) -
There is a two-player word game:
CREATE TABLE players (
uid SERIAL PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE
Even the simpler INSERT statement
INSERT INTO stats(uid, single_q_left)
SELECT player1, COUNT(*)
FROM games
WHERE hand1 = '{Q}'
GROUP BY player1
ON CONFLICT(uid) DO NOTHING;
produces the same error.
On Sat, May 5, 2018 at 4:49 PM, Alexander Farber wrote:
> http://sqlfiddle.com/#!17/7e929/13
>
>
Hi Adrian,
On Sat, May 5, 2018 at 7:03 PM, Adrian Klaver
wrote:
> On 05/05/2018 07:49 AM, Alexander Farber wrote:
>
>> http://sqlfiddle.com/#!17/7e929/13
>>
>> There is a two-player word game:
>>
>> CREATE TABLE players (
>> uid SERI
Thanks, now I see the difference
Good evening,
I am struggling with the syntax, please help.
This query with a CTE works ok:
WITH
extract_letters AS (
SELECT
mid,
STRING_AGG(x->>'letter', '') AS
letters
Oops, I am sorry for the formatting - Mac + Terminal + Gmail :-/
On Sat, May 26, 2018 at 6:37 PM, Adrian Klaver
wrote:
>
>
> https://www.postgresql.org/docs/10/static/sql-update.html
> "column_name
>
> The name of a column in the table named by table_name. The column name
> can be qualified with a subfield name or array subscript, if needed. Do not
> includ
Good evening,
I am trying to SELECT ARRAY_AGG into an array from 2 tables.
But unfortunately here is what I get in PostgreSQL 10.5:
SELECT ARRAY_AGG(hashed)
FROM words_nouns
WHERE added > TO_TIMESTAMP(1539100913)
UNION
SELECT ARRAY_AGG(hashed)
FROM w
Hello, good afternoon!
With PostgreSQL 10 I host a word game, which stores player moves as a JSON
array of objects with properties: col, row, value, letter -
CREATE TABLE words_moves (
mid BIGSERIAL PRIMARY KEY,
action text NOT NULL,
gid integer NOT NULL REFERENCE
Thank you Thomas -
On Mon, Oct 21, 2019 at 4:24 PM Thomas Kellerer wrote:
> Alexander Farber schrieb am 21.10.2019 um 15:39:
> > I am trying to construct a query, which would draw a game board when
> given a move id (aka mid):
> >
> >
Apologies, I should have shown the JSON structure in my very first email -
On Mon, Oct 21, 2019 at 4:45 PM Thomas Kellerer wrote:
> Use ->> to return the value as text (not as JSONB) and you need to use the
> column alias, not the table alias:
>
> (t.tile ->> 'col')::int
>
>
It is a JSON-arr
Thank you -
On Mon, Oct 21, 2019 at 11:20 PM Adrian Klaver
wrote:
> As Thomas pointed there is a difference between -> and ->>:
>
> test_(postgres)# select pg_typeof('[{"one": 1, "two": 2}]'::jsonb -> 0
> -> 'one'), '[{"one": 1, "two": 2}]'::jsonb -> 0 -> 'one';
> pg_typeof | ?column?
> --
Good evening,
for a word game hosted on PostgreSQL 10 I try to find interesting player
moves (high score or played all 7 tiles) and generate a "puzzle" images out
of them (example: https://imgur.com/a/StnXqoR )
The moves are stored in:
words_ru=> \d words_moves
Thank you Michael -
On Fri, Oct 25, 2019 at 7:28 PM Michael Lewis wrote:
> But it prints too many records: all moves cross-multiplied with each other.
>>
>> As if I have forgotten to add 1 more condition to the JOIN LATERAL
>>
>
> LIMIT 1 inside your lateral should resolve that. Personally, I'd
Good evening,
I am trying to count the booleans per each GROUP BY section by the
following stored function:
CREATE OR REPLACE FUNCTION words_list_puzzles(
in_start interval,
in_end interval
) RETURNS TABLE (
out_label text,
Thank you Adrian, but -
On Fri, Nov 29, 2019 at 6:45 PM Adrian Klaver
wrote:
> On 11/29/19 8:38 AM, Alexander Farber wrote:
> >
> > CREATE OR REPLACE FUNCTION words_list_puzzles(
> > in_start interval,
> > in_end interval
>
My context is that I have a table of player moves with PK mid (aka "move
id").
And I am able to find "interesting" moves by the high score or all 7 letter
tiles used.
But I do some human reviewing and set a "puzzle" boolean for truly
interesting moves.
For the reviewing tool I would like to disp
Good morning,
why does not PostgreSQL 10.11 please like the -
CREATE OR REPLACE FUNCTION words_toggle_puzzle(
in_mid bigint
) RETURNS table (
out_puzzle boolean
) AS
$func$
UPDATE words_moves
SET puzzle = NOT puzzle
WHERE
Thank you Patrick -
On Tue, Dec 3, 2019 at 11:49 AM Patrick FICHE
wrote:
>
> It seems that STRICT is the issue.
>
> But why does your function return a table of boolean in this case ?
>
> As it only updates one record, it would probably be easier to return a
> boolean only.
>
> CREATE OR REPLACE
Thanks for your replies!
Tom has hinted that STRICT is pl/pgSQL syntax and not SQL
Regards
Alex
>
Good evening,
in PostgreSQL 11 I have a table holding player moves (could be: 'play',
'swap', 'skip', ...) in a word game:
# \d words_moves;
Table "public.words_moves"
Column | Type | Collation | Nullable |
Default
-+--
Good evening,
I am trying to take a JSONB object (comes from an HTTP cookie set by my
app) and add a property "uid" to it, which should hold an integer:
CREATE OR REPLACE FUNCTION words_get_user(
in_users jsonb,
OUT out_user jsonb
) RETURNS jsonb AS
$func$
Thank you!
Hello,
I am using PostgreSQL 10.15 on CentOS 7 with 64 GB RAM, Intel i7 6700 and I
have the following 2 tables there:
words_ru=> \d words_games
Table "public.words_games"
Column | Type | Collation | Nullable |
Default
--+
Good evening,
hopefully my question is not too stupid, but -
in a 13.1 database I have a words_users table with a boolean column:
-- the user is not allowed to chat or change the motto
muted boolean NOT NULL DEFAULT false,
Currently I check the value as follows, but I wo
On Sat, Dec 5, 2020 at 9:00 PM David G. Johnston
wrote:
> Maybe not “simpler” but for all those checks you could write a single
> query that pulls out all the data at once into a record variable and test
> against the columns pf that instead of executing multiple queries.
>
Thank you!
Good evening,
in PostgreSQL 13.1 I save player moves in the table:
# \d words_moves
Table "public.words_moves"
Column | Type | Collation | Nullable |
Default
-+--+---+--+--
Thank you, Steve -
On Sun, Dec 6, 2020 at 6:50 PM Steve Baldwin
wrote:
> Can't you just use table aliases? So, the outer word_moves would become
> 'word_moves as wm', word_puzzles would become 'word_puzzles as wp', and the
> where clause 'WHERE wp.mid = wm.mid' ?
>
table aliases have worked for
Hello,
for a word puzzle using PostgreSQL 13.1:
https://wortefarbers.de/ws/puzzle2?mid=138&secret=c6f469786df7e8d44461381b62b2ce7d
I am trying to improve a stored function -
CREATE OR REPLACE FUNCTION words_solve_puzzle(
in_mid bigint,
in_uid int,
On Sat, Jan 9, 2021 at 3:49 PM David G. Johnston
wrote:
> Subqueries must be enclosed in parentheses. The parentheses that are part
> of the function call do not count.
>
>
Ah! Thank you David, this has worked now -
CREATE OR REPLACE FUNCTION words_solve_puzzle(
in_mid big
Good evening,
In 13.2 I have 3 SQL queries, which work well and return integer values.
The values I feed to Google Charts (and currently I switch to Chart.js).
Currently I use the queries by calling 3 different custom stored functions
by my Java servlet.
I would like to convert the functions to
On Tue, Feb 16, 2021 at 7:52 PM Michael Lewis wrote:
> Aggregate functions work on a single column to summarize many rows into
> fewer rows. You seem to be wanting to combine multiple columns which would
> be done by concatenation or array[column1,column2] or something like that.
>
Ah right, Mic
Thank you, David, with json_build_array() it works for a single query -
SELECT
JSONB_BUILD_ARRAY(
SUM(CASE WHEN (player1 = in_uid AND state1 = 'won') OR
(player2 = in_uid AND state2 = 'won') THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN (player1 =
Hello, thank you for the helpful replies.
I have decided to go with PL/PgSQL for now and also switched from JSONB
list of lists to map of lists.
And the custom stored function below works mostly well, except for a
special case -
CREATE OR REPLACE FUNCTION words_stat_charts(
in_ui
I have tried the following, but IF FOUND is always false for some reason:
_versus := JSONB_BUILD_ARRAY(
SUM(CASE WHEN (player1 = in_uid AND state1 =
'won') OR (player2 = in_uid AND state2 = 'won') THEN 1 ELSE 0
END)::integer,
SUM(CA
I have ended up with the following (to avoid returning [null, null, null]
for players who never played with each other):
_versus := JSONB_BUILD_ARRAY(
SUM(CASE WHEN (player1 = in_uid AND state1 =
'won') OR (player2 = in_uid AND state2 = 'won') THEN 1 ELSE
Hi Pavel,
why would SELECT INTO be better here?
Thanks
Alex
I have tried switching to SELECT INTO, but IF FOUND is still always true,
which gives me [ null, null, null ] for some users:
SELECT JSONB_BUILD_ARRAY(
SUM(CASE WHEN (player1 = in_viewer AND state1 =
'won') OR (player2 = in_viewer AND state2 = 'won') THEN
Good evening,
I have a word game which uses PostgreSQL 13.2 and 80% of the code is
written as stored functions in PL/PgSQL or SQL.
Recently I have purchased some traffic and the number of daily games
increased from 100 to 700.
In the PostgreSQL log I have noticed that the duration for 2 particul
Yes, Michael, that I have noticed too, but should have written more in my
original mail.
The query when I try it does run in 15ms, but evening logs show the query
(I think only small percentage of it) running 1-3s.
At the same time my CentOS 8 server with 64 GB RAM is never loaded, the
load avera
Thank you all, I will try at first
shared_buffers = 16GBand
index on words_scores(uid, length(word) desc)
:
logical replication launcher
On Sat, Feb 20, 2021 at 4:15 PM Alexander Farber
wrote:
> Thank you all, I will try at first
>
> shared_buffers = 16GBand
>
> index on words_scores(uid, length(word) desc)
>
>
>
Good evening,
I have the following query in 13.2:
# SELECT
TO_CHAR(finished, '-MM-DD') AS day,
SUM(CASE WHEN reason='regular' or reason='resigned' THEN 1
ELSE 0 END)::int AS completed,
SUM(CASE WHEN reason='expired' THEN 1 ELSE 0 END)::int AS
ex
Then I have to split the query in 3 similar ones (with same condition)?
I try:
SELECT
JSONB_AGG(TO_CHAR(finished, '-MM-DD')) AS day
FROM words_games
WHERE finished > CURRENT_TIMESTAMP - INTERVAL '2 week'
GROUP BY day
ORDER BY day;
ERROR: aggreg
Ah, thank you...
JSON support in PostgreSQL is cool and seems to be extended with each
release.
But standard tasks of returning a JSON map of lists or JSON list of list
seem to be difficult to use.
Greetings
Alex
Thank you Thomas, this results in
select
day AS day,
jsonb_agg(completed) AS completed,
jsonb_agg(expired) AS expired
from (
SELECT TO_CHAR(finished, '-MM-DD') AS day,
count(*) filter (where reason in ('regular', 'resigned')) AS
completed,
count
Ahh, thank you all -
select row_to_json (x) FROM( SELECT
jsonb_agg(day) AS day,
jsonb_agg(completed) AS completed,
jsonb_agg(expired) AS expired
from (
SELECT TO_CHAR(finished, '-MM-DD') AS day,
count(*) filter (where reason in ('regular', 'resigned'))
Hello, revisiting an older mail on the too long deletion times (in
PostgreSQL 13.2)...
I have followed the advices here, thank you -
On Fri, Nov 27, 2020 at 4:15 PM Guillaume Lelarge
wrote:
> Le ven. 27 nov. 2020 à 16:05, Alvaro Herrera a
> écrit :
>
>> On 2020-Nov-27, Alexand
Hi Pavel,
trying to follow your advice "You should check so all foreign keys have an
index" I look at the table where I want to delete older records:
# \d words_games
Table "public.words_games"
Column | Type | Collation | Nullable |
Pavel, thank you for asking!
I have put the anonymized dump of my database at:
http://wordsbyfarber.com/words_dev.sql.gz (beware, it is a 1.3 GB download)
The question is why does the command take days (when I tried last time):
delete from words_games where created < now() - interval '12 month'
Thank you, Pavel!
I didn't even think about trying to "explain analyze" deletion of just 1
record -
On Thu, Feb 25, 2021 at 10:04 PM Pavel Stehule
wrote:
> čt 25. 2. 2021 v 19:39 odesílatel Alexander Farber <
>> alexander.far...@gmail.com> napsal:
>>
>
Thank you for the explanation, David
On Thu, Feb 25, 2021 at 9:49 PM David Rowley wrote:
>
> Since your foreign keys perform a cascade delete on the tables
> referencing the tables you're deleting from, any records in those
> referencing tables will be deleted too. You must also look at those
>
Hello,
I have an app using PostgreSQL 13.2, in 6 different human languages (each
using different database, but same source code).
Currently to localize strings return/set by the stored functions I either
get localized strings from a table or maintain stored function source code
in 6 different lan
Yes, good point about the '\$', thank you Tom.
The reason I am trying not to use sed, is because I deploy my database by
executing a single command:
psql words_en < words_en.sql
And the file words_en.sql has the contents:
\i words_hash.sql
\i words_all_letters.sql
\i words_get_hint.sql
\i words
Or is it possible to call external commands from an sql script, like
\i "sed 's/this/that/' some.sql"
Thank you for the \! hint, Pavel, didn't know about that!
Is it possible to have a pure SQL solution? (To avoid having to install
"sed" on my Win 10 PC)
Maybe by using EXECUTE?
EXECUTE REGEXP_REPLACE(
$localize$
CREATE OR REPLACE FUNCTION my_func()
RETURNS text AS
$func$
SELECT
Ah, I understand, that was the wrong EXECUTE, thank you.
Another idea: can't I use \set command for my purpose of localizing stored
functions?
\set my_func_declaration `sed 's/this/that/' my_func.sql`
But how to execute the declaration? I can only echo it with
select (:'my_func_declaration');
I think I will try this approach:
\set localized_declaration `sed 's/this/that/' my_func.sql`
:localized_declaration
Thank you for your input
Thanks for your input
Actually, yes, that is what I have right now a translate() like stored
function, with format %s sometimes.
But that is "at runtime" and I would like to have a "at compile
time"/"deploy once and forget" solution, that is why I have asked about
approaches for modifying the bod
Hello,
I have developed a complete SQL fiddle for my question:
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=dcf063ba1615b392cc3cfa347a32c97b
The context is that I run an online game for two players using a PostgreSQL
14.2 backend.
I would like to make my game more friendly by hiding chat messa
Thank you for replying, David!
The "social dynamic" is needed, because I cannot pass real user id (via
HTTP) to SQL queries.
Instead I pass social network type "social" (like 100 is facebook, 200 is
twitter) and the social network id "sid" returned by that network. This way
noone can read chats b
Hi Ron,
On Wed, May 4, 2022 at 4:56 PM Ron wrote:
>
> How do other web sites know to present only "my" data, even though they
> don't encode "my" user id in the URL?
>
>
that is the usual pattern with OAuth provided by: Facebook, Google, Amazon,
Huawei, etc...
After you auth with them in a game
My real SQL function has one more param, an "auth" string generated by my
game, which complements the social network id "sid".
I have just omitted it in my test case.
>
David, I am trying your suggestion:
On Wed, May 4, 2022 at 4:27 PM David G. Johnston
wrote:
> Assuming the base query is capable of returning all related chat messages
> for both users (I'd probably place that portion into a CTE) the rows you
> want to filter out are those whose c.uid is not my
David, I try then the following -
On Wed, May 4, 2022 at 5:28 PM David G. Johnston
wrote:
> You missed quoting the part where I describe the on clauses you need to
> distinguish between "them" and "me"
>
> Me: u.uid in (player...) and (s.uid = u.uid)
> Them: u.uid in (player...) and (s.uid <> u.
I am probably needing LEFT JOIN LATERAL here (and am completely lost)?
Or to switch to CTE as you suggest
I try with a CTE but cannot figure the syntax:
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=acd6d06a7ea2efc73a0771530832d77e
WITH cte AS (
SELECT uid
FROM words_social
WHERE social = in_social
AND sid = in_sid
LIMIT 1
)
I have tried CROSS JOIN and CASE WHEN (why be greedy, right?):
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=43a33374d15a9330145007702138822c
WITH myself AS (
SELECT uid
FROM words_social
WHERE social = in_social
AND sid = in_sid
I think I am very close with the following CTE, but do not understand how
to bring it into the main SELECT query:
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=ee264dc98b44dee75aa4523164a327b3
WITH myself AS (
SELECT uid
FROM words_social
WHERE social =
Is that the right way to do it?
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=7bd74243397da61ddc4c216ad919c7cc
WITH myself AS (
SELECT uid
FROM words_social
WHERE social = in_social
AND sid = in_sid
LIMIT 1
)
SELE
David, thanks but what do you mean by the last comment -
On Wed, May 4, 2022 at 7:44 PM David G. Johnston
wrote:
> Using (SELECT uid FROM myself) provides the same result without the
> from/join reference; the usage in the case and the where clause could be
> rewritten to use opponent.uid so mys
Good morning, this is a very insightful comment (among many) by you, David -
On Wed, May 4, 2022 at 5:40 PM David G. Johnston
wrote:
> Well, that is basically why I was going on about the oddity of having
> social be a part of the main query. Personally I would write it as
> "myself.uid = in_ui
Good evening, I still have a problem with my JOIN expression -
when I add more games, then messages from other games are displayed:
https://dbfiddle.uk/?rdbms=postgres_14&fiddle=e2ff211f59090d1eeab879498148f907
CREATE OR REPLACE FUNCTION words_get_chat(
in_gid integer,
1 - 100 of 144 matches
Mail list logo
