Add "select * from (values ...)"

[Suor]

There is currently no way to construct such query.

[prust]

@Suor: Sorry for the delay responding.

Do you mean selecting from a set of literal values, like SELECT * FROM ('test1', 'test2', 'test3')? I haven't been able to find anything like that in the SQL92 spec or the sqlite docs. What database are you using?

Or are you referring to a subquery, like this: SELECT * FROM (SELECT * FROM sqlite_master)?

[Suor]

I am using PostgreSQL. That's the kind of query I was trying to make:

INSERT INTO link (job_id, url, source_id, internal)
SELECT job_id::int as job_id, url::text as url,
       source_id::int as source_id, internal::boolean as internal
FROM (VALUES 
    ($1, $3, $2, $4),
    ($1, $5, $2, $6),
    ...
) as tmp (job_id, url, source_id, internal)
WHERE NOT EXISTS
    (SELECT 1 FROM link l where l.job_id = tmp.job_id::int and l.url = tmp.url);

It should also work without INSERT. So both selecting from values and using a subquery.

[prust]

@Suor: I see. It looks like this postgres functionality is documented here: http://www.postgresql.org/docs/9.3/static/queries-values.html.

This isn't supported by SQLite and I'm pretty sure it's not in the SQL-92 standard, which is the base of functionality that I'm committed to supporting in SQLBricks. You're welcome to add this functionality to a postgres-specific extension for SQLBricks. If you find that you need anything added or changed in the core SQLBricks library in order to implement this functionality in an extension, I would be happy to make whatever changes are necessary so that dialect-specific extensions can implement this kind of thing.

[prust]

@Suor: Thank you for bringing this to my attention & documenting this, I didn't know you could use a VALUES command in a SELECT statement in postgres.