Yes. PostgreSQL can execute SQL text, but the method depends on where the string lives: send a query from a client such as psql, run a prepared statement, or use PL/pgSQL’s dynamic EXECUTE inside a function or code block. For values supplied by an application or user, use query parameters rather than assembling SQL by concatenating strings.
What “execute a SQL string” means
A SQL string is text containing a statement, such as SELECT current_date;. PostgreSQL receives SQL text from a client connection, parses it, plans it, and executes it. In that everyday sense, sending a SQL string from an application is a normal way to run a query.
The terminology becomes confusing because several different features are called “execute.” A database client sends a query string to the server; SQL’s EXECUTE command runs a statement that was previously prepared; and PL/pgSQL’s EXECUTE statement runs a dynamically constructed command. These are related, but they are not interchangeable.
PostgreSQL does not provide a general SQL expression that takes an arbitrary text value and executes it as SQL, such as SELECT execute_text(some_text);. Dynamic execution is available in specific contexts, most notably PL/pgSQL. Choosing the right context helps avoid confusing syntax and unsafe query construction.
Send SQL text from a client or psql
If the SQL is already known when you run a command, send it directly from a client. For example, from a terminal with psql installed and a database connection configured:
psql -d mydb -c "SELECT current_date;"
The -c option tells psql to execute the supplied command. You can also put SQL in a file and run it with psql -d mydb -f script.sql, or start an interactive session and type statements at the prompt. A statement normally ends with a semicolon in the input, although psql can also submit a complete statement when it recognizes that it is complete.
In an application, a PostgreSQL driver provides the equivalent operation. For a fixed, trusted query, the application calls the driver’s query method with SQL text. The driver handles the connection and sends the command using PostgreSQL’s client-server protocol. The exact method name and argument format depend on the language and driver.
Be mindful of shell quoting when using psql -c: the shell processes quotes before psql receives the text, and quoting rules differ among shells. For repeated work, a SQL file or an application driver is often easier to maintain than a long command-line string.
Use PREPARE and SQL EXECUTE for repeated queries
At the SQL level, PREPARE defines a named statement with typed parameters, and EXECUTE runs that prepared statement. This is useful when the same statement is run more than once in a database session with different values:
PREPARE order_by_id(bigint) AS
SELECT id, status
FROM orders
WHERE id = $1;
EXECUTE order_by_id(42);
DEALLOCATE order_by_id;
Here, $1 is a parameter in the prepared statement, and the declared type is bigint. EXECUTE order_by_id(42) supplies the value. DEALLOCATE removes the prepared statement; it is also discarded when the session ends. SQL-level prepared statements are not a way to put a text variable containing arbitrary SQL into EXECUTE. The name following EXECUTE must refer to a prepared statement.
Application drivers often offer parameterized query methods that prepare or bind parameters through the client protocol. Use the driver’s documented placeholders and parameter array rather than assuming every library uses PostgreSQL’s SQL-level $1 notation directly. Parameter binding keeps data separate from command text and can also make repeated queries more efficient.
Run dynamic SQL inside PL/pgSQL
When a database function or a DO block must build a command at runtime, PL/pgSQL provides its own EXECUTE statement. The command string can be stored in a variable or assembled with format(). This is particularly useful when the table or column to query varies. For example:
DO $$
DECLARE
table_name text := 'orders';
minimum_total numeric := 100;
matching_rows bigint;
BEGIN
EXECUTE format(
'SELECT count(*) FROM %I WHERE total >= $1',
table_name
)
INTO matching_rows
USING minimum_total;
RAISE NOTICE 'Matching rows: %', matching_rows;
END
$$;
The block forms a query using the table name, runs it, stores the returned count in matching_rows, and supplies the comparison value with USING. In PL/pgSQL dynamic commands, $1, $2, and so on represent values supplied by USING. The INTO clause assigns the query result to a PL/pgSQL variable; it is not the same as creating a table with SQL’s SELECT INTO form.
Use dynamic SQL only when the command structure really needs to vary. If the query is fixed, an ordinary PL/pgSQL SQL statement is simpler and may allow PostgreSQL to reuse a cached plan. A PL/pgSQL dynamic EXECUTE is planned when it is run, which can be useful when changing inputs affect the best plan, but adds planning work.
Keep values and identifiers separate
SQL values and SQL identifiers have different roles. A value is data, such as a date, number, or name being searched for. An identifier names a database object, such as a table or column. Query parameters can stand in for values, but they cannot stand in for table or column names.
For PL/pgSQL dynamic SQL, pass data values through USING. For identifiers, use format() with the %I conversion, which quotes an identifier correctly. If a schema and table name are both variable, format them as separate identifiers, for example format('%I.%I', schema_name, table_name). Do not treat a schema-qualified name as one identifier: quoting the whole string would refer to an object whose name contains a dot.
For a literal value that must be inserted into generated SQL text, format() has %L, which quotes a literal. However, parameter placeholders with USING are generally preferable for values because they keep data out of the command string. Never assume that adding quotation marks around untrusted text makes string concatenation safe.
Prevent SQL injection
SQL injection occurs when untrusted input changes the meaning of a command instead of remaining data. For example, concatenating a user-provided search term directly into a WHERE clause can allow quotation marks or SQL syntax in that input to alter the query. The same risk applies to dynamic SQL inside the database as it does to SQL assembled in an application.
- Use parameterized queries for data values in application code.
- In PL/pgSQL, pass dynamic-query values with
USING. - For variable identifiers, use identifier-aware quoting such as
format('%I', name), and validate that the requested object is allowed. - Give application database roles only the permissions they need; quoting alone does not make an unauthorized operation acceptable.
- Avoid executing arbitrary SQL submitted by ordinary users. Parameterization does not turn user-provided SQL code into harmless data.
Parameters protect values, not the overall intent of a query. If a user can choose a sort column, for example, a placeholder cannot replace that column name. Map the user’s choice to a fixed allowlist of permitted identifiers, then quote the selected identifier correctly. This prevents both injection and accidental access to an unintended column.
Transactions, results, and errors
Executing a string does not by itself decide whether its effects should be permanent. PostgreSQL normally runs each statement in a transaction; explicit BEGIN, COMMIT, and ROLLBACK let a client group operations so they succeed or fail together. Check the transaction behavior of the client or framework as well: some tools manage transactions automatically.
A query that returns rows must have its results fetched or assigned. In PL/pgSQL, dynamic EXECUTE can use INTO for a single row target, while commands that produce a set of rows may call for a cursor or a loop, depending on the task. If a query can return multiple rows but the code expects one, decide how that case should be handled. PL/pgSQL’s INTO STRICT can require exactly one row and raises an error otherwise.
Errors can come from invalid syntax, missing tables, incompatible types, permissions, or constraints. PL/pgSQL can handle selected errors with an EXCEPTION block, but broad error handling that hides failures can make data problems difficult to diagnose. During development, preserve useful error information and test both the expected result and failure cases.
Choose the method that fits the job
- One-off query or script: use
psql -c, an interactivepsqlsession, or a SQL file withpsql -f. - Application query with changing data: use the driver’s parameterized query API.
- Repeated SQL command in a session: consider SQL
PREPAREandEXECUTE, or the driver’s prepared-statement support. - Function that must vary a table or other SQL structure: use PL/pgSQL dynamic
EXECUTE, quote identifiers, and bind values withUSING. - Fixed query inside PL/pgSQL: write the SQL statement directly rather than converting it to a string unnecessarily.
These options solve different problems. For instance, a prepared statement is not required just to pass a value safely from an application; parameterized driver queries are usually the natural choice. Conversely, parameter binding cannot make a variable table name work, because the database must identify the table while parsing the command. That case calls for carefully constructed dynamic SQL.
Frequently Asked Questions
Can I run a SQL string stored in a PostgreSQL text variable?
Not as a general SQL expression. In PL/pgSQL, use dynamic EXECUTE to run a command string. In an application, send the string through a PostgreSQL driver. SQL’s EXECUTE command instead runs a named statement created with PREPARE.
What is the difference between EXECUTE and EXECUTE format()?
In PL/pgSQL, EXECUTE runs a command string, while format() is a function that constructs text. You can write EXECUTE format(...) to build and run a command, but use %I for identifiers and USING for values where possible.
Can a query parameter represent a table name?
No. Parameters represent data values, not SQL syntax such as table or column identifiers. For a variable table name in PL/pgSQL, construct the command with identifier quoting such as format('%I', table_name), and restrict which names are accepted.
Is PostgreSQL EXECUTE the same as PL/pgSQL EXECUTE?
No. SQL EXECUTE statement_name(...) runs a previously prepared statement. PL/pgSQL’s EXECUTE command_string runs a dynamically supplied command within procedural code. The syntax and purpose differ.
How do I execute several SQL statements from a file?
Put the statements in a plain-text SQL file and run psql -d mydb -f script.sql. Include semicolons to terminate SQL statements. For application code, use the driver’s documented behavior rather than assuming that a single query call accepts multiple statements.
Related
- JavaScript split() Method: Turn a String Into an Array
- SQL Server STRING_AGG: Syntax, Examples, and Limits
- How to Split an Activity in P6: Step-by-Step Guide
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Lock Cells in Google Sheets: How Cell Protection WorksYes. In Google Sheets, use Data > Protect sheets and ranges to restrict editing of selected cells or…
- Google Sheets IF String Contains: Formulas and ExamplesTo make Google Sheets return one value when a cell contains text and another when it does not,…
- Badge Inventory Spreadsheet: Setup and TrackingA badge inventory spreadsheet is a workbook for recording the badges your organization owns, where they are, and…
- How to Group Same Names in Excel by Sorting or SummarizingTo group identical names together in Excel, select the entire data range, choose Data > Sort, and sort…
- Archive Outlook Emails to a Local Folder as a PST FileYes. In classic Outlook for Microsoft 365 on Windows, export the messages to an Outlook Data File (.pst)…
- How to Add a Header in Microsoft WordTo add a header in Microsoft Word, open the document, select Insert > Header, choose a built-in layout,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.