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:
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 $1You 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 aPREPARE
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
Fetch a Record on Maximum Date of Every Month
In Ms SQL Server, How to "Atomically" Increment a Column Being Used as a Counter
Stored Procedure with Variable Number of Parameters
Sqllite Strftime Function to Get Grouped Data by Months
Implementing a Total Order Ranking in Postgresql 8.3
How to Check Type of Value in Postgres
Performing a Query on a Result from Another Query
Good Reasons Not to Use a Relational Database
Join Tables on Columns of Composite Foreign/Primary Key in a Query
Please Correct Me with the SQL Query
Update Table with a Subquery Which Is Returning More Than One Row