Postgresql Syntax Check Without Running the Query

PostgreSQL syntax check without running the query

I recently wrote up a utility to statically check the syntax of SQL for PostgreSQL. It leverages ecpg, the embedded SQL C preproccessor for postgres, to check the SQL syntax, so it uses the exact same parser that is built in to Postgres itself.

You can check it out on github: http://github.com/markdrago/pgsanity. You can give the README a skim to get a better idea of how it works and to get directions for how to install it. Here's a short example of how pgsanity can be used:

$ pgsanity good1.sql good2.sql bad.sql
bad.sql: line 1: ERROR: syntax error at or near "bogus_token"

$ find -name '*.sql' | xargs pgsanity
./sql/bad1.sql: line 59: ERROR: syntax error at or near ";"
./sql/bad2.sql: line 41: ERROR: syntax error at or near "insert"
./sql/bad3.sql: line 57: ERROR: syntax error at or near "update"

Running SQL script through psql gives syntax errors that don't occur in PgAdmin

Run your file 00101-CreateStateTable.sql through a hex dumper. I'll bet you have a UTF-16 marker at the beginning of the file (before the "--" comment characters).

Postgresql Check if query is still running

Select * from pg_stat_activity where state not ilike 'idle%' and query ilike 'insert%'

This will return all non-idle sessions where the query begins with insert, if your query does not show in this list then it is no longer running.

pg_stat_activity doc

How to re-check an SQL function created with check_function_bodies=false?

I can think of two ways:

  1. You can call the language validator function directly:

    SELECT lanname, lanvalidator::regprocedure FROM pg_language;

    lanname | lanvalidator
    ------------+------------------------------
    internal | fmgr_internal_validator(oid)
    c | fmgr_c_validator(oid)
    sql | fmgr_sql_validator(oid)
    plpgsql | plpgsql_validator(oid)
    plpython3u | plpython3_validator(oid)
    (5 rows)

    For SQL functions, that would work like this:

    SET check_function_bodies = off;
    CREATE FUNCTION bad() RETURNS void LANGUAGE sql AS 'SELECT $1';

    SET check_function_bodies = on;
    SELECT fmgr_sql_validator('bad()'::regprocedure);

    ERROR: there is no parameter $1
    LINE 1: SELECT $1
    ^
    QUERY: SELECT $1
  2. You can redefine the function and check if it throws an error:

    SET check_function_bodies = on;
    DO $$BEGIN
    EXECUTE pg_get_functiondef('bad()'::regprocedure);
    END;$$;

PostgreSQL syntax error when using EXECUTE in Function

I think your problem is the language you're using. EXECUTE in the SQL language:

EXECUTE is used to execute a previously prepared statement. Since prepared statements only exist for the duration of a session, the prepared statement must have been created by a PREPARE statement executed earlier in the current session.

isn't the same as EXECUTE in PL/pgSQL:

Oftentimes you will want to generate dynamic commands inside your PL/pgSQL functions, that is, commands that will involve different tables or different data types each time they are executed. PL/pgSQL's normal attempts to cache plans for commands (as discussed in Section 39.10.2) will not work in such scenarios. To handle this sort of problem, the EXECUTE statement is provided:

EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];

You're using the SQL EXECUTE (which executes a prepared statement) when you want to be using the PL/pgSQL EXECUTE (which executes a string as SQL).

Try this:

CREATE OR REPLACE FUNCTION example() RETURNS void AS $$
BEGIN
EXECUTE 'INSERT INTO table1 (col1, col2, col3) SELECT col1, col2, col3 from temp_table';
END;
$$ LANGUAGE PLPGSQL;

Or, another example that seems closer to what you seem to be trying to do:

create or replace function example(tname text) returns void as $$
begin
execute 'insert into ' || tname || ' (name) values(''pancakes'')';
end;
$$ language plpgsql;

That will insert 'pancakes' into the table that you pass in the tname argument to the function.

Postgresql : syntax error at or near -

I have reproduced the issue in my system,

postgres=# alter user my-sys with password 'pass11';
ERROR: syntax error at or near "-"
LINE 1: alter user my-sys with password 'pass11';
^

Here is the issue,

psql is asking for input and you have given again the alter query see postgres-#That's why it's giving error at alter

postgres-# alter user "my-sys" with password 'pass11';
ERROR: syntax error at or near "alter"
LINE 2: alter user "my-sys" with password 'pass11';
^

Solution is as simple as the error,

postgres=# alter user "my-sys" with password 'pass11';
ALTER ROLE

Is there a command to test an SQL query without executing it? ( MySQL or ANSI SQL )

I realise this is a bit of an old question but for completeness...

If the intention is to find the query processing time without returning any rows (I need this quite often, I want to know how long a piece of code I am using will take without having it return a couple of million rows I am not interested in seeing) then the BLACKHOLE engine can be very useful:

https://dev.mysql.com/doc/refman/8.0/en/blackhole-storage-engine.html

For instance say I have 2 tables, t1 & t2, with millions of rows, that I am joining together. I want to check how long this is likely to take, in a GUI (SQLYog or mysql workbench or somesuch) without returning millions of rows that will eat up memory and presumably take time for the GUI to process and display. I use the blackhole engine to 'dump' the rows to nowhere.
EG:

CREATE TABLE tBH (a TINYINT) ENGINE = BLACKHOLE;
SELECT NOW(); -- Show start time
INSERT tBH
SELECT 1 FROM t1
LEFT JOIN t2 ON t1.key1 = t2.key1;
SELECT NOW(); -- Show end time

Note that as I am just looking for execution time I do not bother returning all the columns (IE with "*") but just a placeholder ("1" in this case).



Related Topics



Leave a reply



Submit