What does this select statement actually do?

Question

I'm reviewing log of executed PostgreSQL statements and stumble upon one statement I can't totally understand. Can somebody explain what PostgreSQL actually do when such query is executed? What is siq_query?

select * 
from siq_query('', '21:1', '', '("my search string")', False, True, 'http://siqfindex:8080/storediq/findex')

I'm running PostgreSQL 9.2

If you don't know what `siq_query` is in this context, you would benefit from reading about SQL fundamentals. — Jules, Dec 21 '17 at 16:41
I'm familiar with SQL fundamentals. Usually after FROM name of the Table or View or another select statement is following. This time this is something different. What http string is doing there? — Marvin Divo, Dec 21 '17 at 16:45
The URL is just a string. It has no special meaning to SQL. Ideally, it's data which exists in a column in a table somewhere in your database. `siq_query` appears to be (without context) a [user-defined function](https://www.postgresql.org/docs/current/static/sql-createfunction.html), returning some collection of records based on the parameters you pass it. More context would allow the community to give thorougher answers. — Jules, Dec 21 '17 at 16:49

Erwin Brandstetter · Accepted Answer · 2017-12-21T17:33:33.200

siq_query(...) is a server-side function taking 7 input parameters (or more). It's not part of any standard Postgres distribution I know (certainly not mainline Postgres 9.2), so it has to be user-defined or part of some extension you installed. It does whatever is defined in the function. This can include basically anything your Postgres user is allowed to do. Unless it's a SECURITY DEFINER function, then it ca do whatever the owner of the function is allowed to do.

The way it is called (SELECT * FROM), only makes sense if it returns multiple rows and/or columns, most likely a set of rows, making it a "set-returning function", which can be used almost like a table in SQL queries.

Since the function name is not schema-qualified, it has to reside in a visible schema. See:

How does the search_path influence identifier resolution and the "current schema"

Long story short, you need to see the function definition to know what it does exactly. You can use psql (\df+ siq_query), pgAdmin (browse and select it to see its definition in the SQL pane) or any other client tool to look it up. Or query the system catalog pg_proc directly:

SELECT * FROM pg_proc WHERE proname = 'siq_query';

Pay special attention to the column prosrc, which holds the function body for some languages like plpgsql.

There might be multiple variants of that name, Postgres allows function overloading.

What does this select statement actually do?

1 Answers1