1

I'm trying this script, in ? passed GeoJson

DO $$
DECLARE
    lib_table_name text;
    lib_table_id text;
    geom geometry;
BEGIN
    SELECT
        point_node_libs.lib_type,
        point_node_libs.lib_type_id,
        nodes.geometry
        INTO lib_table_name, lib_table_id, geom
        FROM point_nodes AS nodes
        LEFT JOIN point_node_libs
        ON point_node_libs.id = nodes.node_lib_id
        WHERE ST_DWithin(geom, ST_GeomFromGeoJSON(?), 0);

    EXECUTE format('
    SELECT
        id,
        geometry,
        metadata,
        draft,
        node_lib_id AS nodeLibId,
        created_at AS createdAt,
        updated_at AS updatedAt,
        point_node_libs.*,
        lib_table.*
        FROM point_nodes AS points
        LEFT JOIN point_node_libs
        ON point_node_libs.id = points.node_lib_id
        LEFT JOIN (
            SELECT *
            FROM %I
            WHERE id=%s
        ) lib_table
        WHERE $1;', lib_table_name, lib_table_id) USING (ST_DWithin(geom, ST_GeomFromGeoJSON(?), 0));
END $$;

and the error is

[22004] ERROR: null values cannot be formatted as an SQL identifier Where: PL/pgSQL function inline_code_block line 17 at EXECUTE

DB: Postgre 13.9

Tigran Vardanyan
  • 46
  • 3
  • 1) `lib_table_name` is `NULL` hence the error. 2) Why not `... WHERE id=$1 ... lib_table WHERE $2` and then `USING (lib_table_id,ST_DWithin(geom, ST_GeomFromGeoJSON(?), 0))` and remove `lib_table_id` from the `format`. – Adrian Klaver Jan 30 '23 at 23:23
  • The error message is clear, isn't it? Don't pass NULL values. – Laurenz Albe Jan 31 '23 at 06:36

0 Answers0