PostgreSQL多语句执行方法：条件查询报错解决与DDL/DML单执行方案

Hey there! Let's tackle your PostgreSQL questions one by one.

First, let's fix that syntax error with your conditional query. The IF statement you're using is part of PL/pgSQL (PostgreSQL's procedural SQL)—it can't be run directly in a plain SQL query. Plain SQL is declarative, so we need to adjust how we handle conditional logic here.

Fixing the Conditional Query #

You have a couple of options depending on your needs:

Option 1: Use a CASE Expression (Simple Scenarios)

If dev and dev1 have identical column structures, you can use a CASE in the FROM clause to pick the right table, or combine queries with UNION ALL:

-- Option A: CASE in FROM clause
SELECT *
FROM CASE WHEN extract(dow from current_date) = 1 THEN dev ELSE dev1 END;

-- Option B: UNION ALL with conditional filters
SELECT * FROM dev WHERE extract(dow from current_date) = 1
UNION ALL
SELECT * FROM dev1 WHERE extract(dow from current_date) != 1;

Both work, but make sure the tables have matching columns (same count, data types, and order) to avoid errors.

Option 2: Use a PL/pgSQL DO Block or Function (Complex Logic)

If you need more control (like running multiple statements or handling side effects), use a DO block for one-time execution:

DO $$
BEGIN
    IF extract(dow from current_date) = 1 THEN
        RAISE NOTICE 'Fetching data from dev table';
        PERFORM * FROM dev; -- Use PERFORM for queries that don't need to return results
    ELSE
        RAISE NOTICE 'Fetching data from dev1 table';
        PERFORM * FROM dev1;
    END IF;
END $$;

If you need to return the query results, create a reusable function instead:

CREATE OR REPLACE FUNCTION get_dev_records()
RETURNS SETOF dev -- Matches the structure of the dev table
AS $$
BEGIN
    IF extract(dow from current_date) = 1 THEN
        RETURN QUERY SELECT * FROM dev;
    ELSE
        RETURN QUERY SELECT * FROM dev1;
    END IF;
END $$ LANGUAGE plpgsql;

-- Call the function to get results
SELECT * FROM get_dev_records();

Executing Multiple Statements & Combining DDL/DML #

Now, let's cover how to run multiple statements and mix DDL (like CREATE TABLE) with DML (like INSERT, UPDATE) in a single execution.

Running Multiple Statements

It's straightforward—just separate each statement with a semicolon (;). This works in psql, pgAdmin, or most database clients:

-- Example: Two SELECT queries
SELECT * FROM dev LIMIT 5;
SELECT * FROM dev1 LIMIT 5;

-- Example: DML followed by a query
UPDATE dev SET status = 'active' WHERE id < 10;
SELECT * FROM dev WHERE id < 10;

In application code (like Python with psycopg2), make sure your driver supports batch execution—most do, as long as you pass the full semicolon-separated string.

Combining DDL & DML in One Execution

PostgreSQL lets you mix DDL and DML in the same execution batch. You can even wrap them in a transaction to ensure atomicity (all operations succeed or none do):

-- Example: Create a table (DDL) then insert data (DML)
CREATE TABLE temp_audit (log_id SERIAL, event_time TIMESTAMP, action TEXT);
INSERT INTO temp_audit (event_time, action) VALUES (NOW(), 'Table created');
SELECT * FROM temp_audit;

-- Example: Atomic transaction for DDL + DML
BEGIN;
ALTER TABLE dev ADD COLUMN last_modified TIMESTAMP;
UPDATE dev SET last_modified = NOW() WHERE last_modified IS NULL;
COMMIT; -- Use ROLLBACK instead if you need to undo changes

For procedural logic involving both DDL and DML, you can also use a DO block:

DO $$
BEGIN
    -- DDL: Create a log table if it doesn't exist
    CREATE TABLE IF NOT EXISTS app_log (log_time TIMESTAMP, message TEXT);
    -- DML: Insert a log entry
    INSERT INTO app_log VALUES (NOW(), 'Log table checked/created');
    -- Another DML: Update a record
    UPDATE dev SET status = 'updated' WHERE id = 1;
END $$;

内容的提问来源于stack exchange，提问作者Suresh Alathuru