PostgreSQL存储过程嵌套调用及执行异常问题咨询

Let's break down what's going wrong and how to fix your procedure1 function (note: in PostgreSQL, this is technically a function, not a stored procedure—important for correct calling syntax later).

Core Problems Identified #

1. Uninitialized variables breaking inserts: You're using f_primary_key and fid in your INSERT statement without assigning any values to them. When rowscount <> datacount, this throws a null value error that kills the function before it can finish any work, including never reaching the else branch that calls procedure2.
2. Return statement context issues: Keeping the return statement isn't the problem itself—but if the insert branch throws an error, the function crashes before hitting the return or the else block. Removing the return statement breaks the function entirely, since you declared it RETURNS text and PostgreSQL requires a valid return value for functions.
3. Dynamic SQL safety & clarity: Your raw string concatenation for SQL statements is risky (SQL injection) and prone to syntax errors if table names have special characters.

Step-by-Step Fixes #

1. Initialize Unused Variables #

First, assign values to f_primary_key and fid based on your business logic. For example, if you need to extract the primary key from the JSON data you're fetching:

FOR inddata IN SELECT * FROM jsonb_array_elements(fpo_data) LOOP
    -- Extract primary key from the JSON structure (adjust path to match your data)
    SELECT (inddata->0->>'fc_pkey1') INTO f_primary_key;
    -- Assign fid (example: use the foseqid from the JSON)
    SELECT (inddata->1) INTO fid;

    EXECUTE format('INSERT INTO %I(fc_pkey1, fpo_data, fid) VALUES ($1, $2, $3)', 
        tb_name || '_' || file_version || '_' || compare_file_version || '_pk')
    USING f_primary_key, inddata, fid;
END LOOP;

2. Add Error Handling & Fix Control Flow #

Wrap the insert logic in an exception block to catch errors, so the function doesn't crash silently. This also ensures you can still reach the else branch or return a meaningful message:

CREATE OR REPLACE FUNCTION procedure1(tb_name text, compare_tb_name text, file_version text, compare_file_version text) 
RETURNS text 
LANGUAGE 'plpgsql' 
COST 100 VOLATILE AS $BODY$
DECLARE 
    createquery text; 
    fpo_data jsonb; 
    inddata jsonb; 
    f_primary_key text; 
    rowscount INTEGER; 
    datacount INTEGER; 
    fid INTEGER; 
BEGIN 
    -- Use format() for safe dynamic SQL
    createquery := format('CREATE TABLE IF NOT EXISTS %I( 
        id serial PRIMARY KEY, 
        fc_pkey1 VARCHAR (250) NULL, 
        fc_pkey2 VARCHAR (250) NULL, 
        fc_pkey3 VARCHAR (250) NULL, 
        fpo_data TEXT NULL, 
        fid INTEGER NULL 
    )', tb_name || '_' || file_version || '_' || compare_file_version || '_pk');
    EXECUTE createquery;

    EXECUTE format('SELECT count(*) FROM %I', tb_name || '_' || file_version || '_' || compare_file_version || '_pk') INTO rowscount;
    EXECUTE format('SELECT count(*) FROM %I', tb_name) INTO datacount;

    IF(rowscount <> datacount) THEN
        BEGIN
            EXECUTE format('SELECT json_agg((fpdata, foseqid))::jsonb FROM (SELECT fo_data AS fpdata, fo_seq_id as foseqid FROM %I LIMIT 1000 ) t', tb_name) INTO fpo_data;
            
            FOR inddata IN SELECT * FROM jsonb_array_elements(fpo_data) LOOP
                -- Initialize variables (adjust to match your actual data structure)
                SELECT (inddata->0->>'fc_pkey1') INTO f_primary_key;
                SELECT (inddata->1) INTO fid;

                EXECUTE format('INSERT INTO %I(fc_pkey1, fpo_data, fid) VALUES ($1, $2, $3)', 
                    tb_name || '_' || file_version || '_' || compare_file_version || '_pk')
                USING f_primary_key, inddata, fid;
            END LOOP;
        EXCEPTION
            WHEN OTHERS THEN
                RAISE NOTICE 'Insert failed: %', SQLERRM;
                RETURN 'Primary Key Generation failed: ' || SQLERRM;
        END;
    ELSE
        PERFORM procedure2(tb_name, compare_tb_name, file_version, compare_file_version);
    END IF;

    RETURN 'Primary Key Generation completed';
END; 
$BODY$;

3. Correct Function Calling Syntax #

From your application, call the function correctly (since it returns a single text value, not a result set):

SELECT procedure1('table_version_1', 'table_version_2', 'v1', 'v2');

Using select * from procedure1() would only work if your function returned a set of rows, which it doesn't.

Bonus: Reduce SQL Injection Risk #

Always use format() with %I for identifiers (table/column names) and %L for literal values when building dynamic SQL. This prevents injection attacks and handles edge cases like table names with spaces or special characters.

内容的提问来源于stack exchange，提问作者Sai sri