Captures the query text for SQL commands.
Query the STL_QUERYTEXT table to capture the SQL that was logged for the following statements:
SELECT, SELECT INTO
INSERT, UPDATE, DELETE
CREATE TABLE AS (CTAS)
To query activity for these statements over a given time period, join the STL_QUERYTEXT and STL_QUERY tables.
The STL_QUERY and STL_QUERYTEXT tables only contain information about queries, not other utility and DDL commands. For a listing and information on all statements executed by Amazon Redshift, you can also query the STL_DDLTEXT and STL_UTILITYTEXT tables. For a complete listing of all statements executed by Amazon Redshift, you can query the SVL_STATEMENTTEXT view.
This table is visible to all users. Superusers can see all rows; regular users can see only their own data. For more information, see Visibility of Data in System Tables and Views.
|Column Name||Data Type||Description|
|userid||integer||ID of the user who generated the entry.|
|query||integer||Query ID. The query column can be used to join other system tables and views.|
|pid||integer||Process ID. Normally, all of the queries in a session are run in the same process, so this value usually remains constant if you run a series of queries in the same session. Following certain internal events, Amazon Redshift might restart an active session and assign a new PID. For more information, see STL_RESTARTED_SESSIONS. You can use this column to join to the STL_ERROR table.|
|sequence||integer||When a single statement contains more than 200 characters, additional rows are logged for that statement. Sequence 0 is the first row, 1 is the second, and so on.|
|text||character(200)||SQL text, in 200-character increments.|
You can use the PG_BACKEND_PID() function to retrieve information for the current session. For example, the following query returns the query ID and a portion of the query text for queries executed in the current session.
select query, substring(text,1,60) from stl_querytext where pid = pg_backend_pid() order by query desc; query | substring -------+-------------------------------------------------------------- 28262 | select query, substring(text,1,80) from stl_querytext where 28252 | select query, substring(path,0,80) as path from stl_unload_l 28248 | copy category from 's3://dw-tickit/manifest/category/1030_ma 28247 | Count rows in target table 28245 | unload ('select * from category') to 's3://dw-tickit/manifes 28240 | select query, substring(text,1,40) from stl_querytext where (6 rows)