[pgSQL] Missing FROM clause

[Dorgaldir]

Ik heb een trigger op een table staan in een postgres DB, in de functie die aangeroepen word door die trigger staat een query die van de desbetreffende table de primary key column teruggeeft.
Deze query werkt als ik die gewoon uitvoer als sql:

SQL:

SELECT  a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
FROM    pg_index AS i
JOIN    pg_attribute AS a ON a.attrelid = i.indrelid
    AND a.attnum = ANY(i.indkey)
WHERE   i.indrelid = 'collection'::regclass
    AND     i.indisprimary

Dit werkt prima, krijg ik mijn resultaat terug zonder problemen, echter, als ik deze query in de functie giet die getriggert word, dan werkt het niet...

SQL:

DECLARE
    v_data JSON;

select to_json(ARRAY(
    SELECT to_json(t) FROM (
    (SELECT     a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
    FROM    pg_index AS i
    JOIN    pg_attribute AS a ON a.attrelid = i.indrelid
          AND a.attnum = ANY(i.indkey)
    WHERE   i.indrelid = 'collection'::regclass
          AND       i.indisprimary)
    ) t
)) INTO v_data;

Dan krijg ik deze error:

code:

SQLSTATE: 42P01, SQLERRM: missing FROM-clause entry for table "a"

En ik vind maar niet waarom het op de ene plaats wel werkt en op de andere niet, iemand die me hiermee kan helpen?

[DJMaze]

Kijk eens goed naar je haakjes

[ Voor 67% gewijzigd door DJMaze op 16-08-2017 14:54 ]

[Dorgaldir]

Heb de dubbele haakjes rond de query weggehaald, maar zelfde resultaat.

SQL:

select to_json(ARRAY(
        SELECT to_json(t) FROM (
            SELECT  a.attname , format_type(a.atttypid, a.atttypmod) AS data_type
            FROM    pg_index AS i
            JOIN    pg_attribute AS a ON a.attrelid = i.indrelid
                AND a.attnum = ANY(i.indkey)
            WHERE   i.indrelid = 'collection'::regclass
            AND     i.indisprimary
        ) t
    )) INTO v_data;

[Harrie_]

Wat gebeurt er als je

SQL:

            FROM    pg_index AS i
            JOIN    pg_attribute AS a ON a.attrelid = i.indrelid

vervangt door

SQL:

            FROM    pg_index i
            JOIN    pg_attribute a ON a.attrelid = i.indrelid

?

edit:
En als je de JOIN wijzigt door INNER JOIN?
Ik roep ook maar wat

[ Voor 14% gewijzigd door Harrie_ op 16-08-2017 15:16 ]

[Dorgaldir]

Die AS heb ik later toegevoegd omdat het zonder ook niet werkte, heb nu met INNER geprobeerd en dat geeft nog steeds dezelfde error...

[DJMaze]

code:

)) FROM DUAL INTO v_data;

[DixxyJS]

Harrie_ schreef op woensdag 16 augustus 2017 @ 15:04:

edit:
En als je de JOIN wijzigt door INNER JOIN?
Ik roep ook maar wat

De 'AS' is hetzelfde als zonder 'AS', default is hetgeen wat je erachter zet ook een alias.
Hetzelfde geld voor de 'INNER JOIN', default is een 'JOIN' al een' INNER JOIN'.

Kun je aan de hand van de error niet achterhalen wat er fout gaat?

[ Voor 30% gewijzigd door DixxyJS op 16-08-2017 15:37 ]

[Harrie_]

DixxyJS schreef op woensdag 16 augustus 2017 @ 15:35:
[...]


De 'AS' is hetzelfde als zonder 'AS', default is hetgeen wat je erachter zet ook een alias.
Hetzelfde geld voor de 'INNER JOIN', default is een 'JOIN' al een' INNER JOIN'.

Kun je aan de hand van de error niet achterhalen wat er fout gaat?

Daar ging ik ook van uit, daarom ook de opmerking 'ik roep ook maar wat'. Ik werk eigenlijk nooit met PostgreSQL maar ik weet dat er soms van die hele lullige kleine vershillen tussen MySQL en MSSQL zitten.

Zo kun je bijv. MSSQL direct strings joinen maar moet je er in MySQL een CONCAT() omheenwrappen. Ik dacht dus puur om het uitsluiten van misschien maar even kijken of het aan de AS / INNER ligt.

[Dorgaldir]

DJMaze schreef op woensdag 16 augustus 2017 @ 15:34:

code:

)) FROM DUAL INTO v_data;

Dan krijg ik "Relation "Dual" does not exist"

[Robicide]

En als je je subquery in de FROM clause stopt?

code:

DECLARE
    v_data JSON;

select to_json(ARRAY(x.jsonvalue))
INTO v_data
FROM
(
    SELECT to_json(t) as jsonvalue 
    FROM 
    (
        SELECT  a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
        FROM    pg_index AS i
        JOIN    pg_attribute AS a 
          ON    a.attrelid = i.indrelid
          AND   a.attnum = ANY(i.indkey)
        WHERE   i.indrelid = 'collection'::regclass
          AND   i.indisprimary
    ) as t
) as x

[Dorgaldir]

Robicide schreef op vrijdag 18 augustus 2017 @ 17:00:
En als je je subquery in de FROM clause stopt?

code:

DECLARE
    v_data JSON;

select to_json(ARRAY(x.jsonvalue))
INTO v_data
FROM
(
    SELECT to_json(t) as jsonvalue 
    FROM 
    (
        SELECT  a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
        FROM    pg_index AS i
        JOIN    pg_attribute AS a 
          ON    a.attrelid = i.indrelid
          AND   a.attnum = ANY(i.indkey)
        WHERE   i.indrelid = 'collection'::regclass
          AND   i.indisprimary
    ) as t
) as x

Dan krijg ik deze melding:


ben nu aan het kijken hoe ik die oplos om te zien of het werkt

[Dorgaldir]

Ok, ik heb het in orde gekregen Dit is de uiteindelijke code die ik gebruik en dat werkt toch, ik krijg het resultaat dat ik wil.

code:

select to_json(x)
    INTO v_data
    FROM
    (
        SELECT  a.attname, format_type(a.atttypid, a.atttypmod) AS data_type
        FROM    pg_index AS i
        JOIN    pg_attribute AS a 
          ON    a.attrelid = i.indrelid
          AND   a.attnum = ANY(i.indkey)
        WHERE   i.indrelid = 'collection'::regclass
          AND   i.indisprimary
    ) as x;

Dank voor alle hulp

[ Voor 7% gewijzigd door Dorgaldir op 22-08-2017 15:49 ]