"CREATE TABLE table_name AS EXECUTE name WITH DATA" becomes syntax error.

On Thursday, November 24, 2011 1:55:53 am Naoya Anzai wrote:

Hi,

Accroding to
http://www.postgresql.org/docs/9.1/interactive/sql-createtableas.html ,
"CREATE TABLE table_name AS EXECUTE name WITH DATA" seems a right syntax,
but,this statement becomes a SYNTAX ERROR.
Is this a specification?
---
naoya=# SELECT VERSION();
version
---------------------------------------------------------------------------
------------------------------------ PostgreSQL 9.1.1 on
x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat
4.1.2-48), 64-bit (1 row)

naoya=# PREPARE GETONE AS SELECT * FROM SEED WHERE ID=1;
PREPARE

naoya=# EXECUTE GETONE;
id | date
----+----------------------------
1 | 2011-11-24 11:24:49.675427
(1 row)

naoya=# CREATE TABLE NEW_SEED AS EXECUTE GETONE;
SELECT 1

naoya=# CREATE TABLE NEW_SEED2 AS EXECUTE GETONE WITH DATA;
ERROR: syntax error at or near "WITH DATA" at character 42
STATEMENT: CREATE TABLE NEW_SEED2 AS EXECUTE GETONE WITH DATA;

Order of execution?

Example from link above:
PREPARE recentfilms(date) AS
SELECT * FROM films WHERE date_prod > $1;
CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS
EXECUTE recentfilms('2002-01-01');

So for your case:
CREATE TABLE NEW_SEED2 WITH DATA AS EXECUTE GETONE;

---

Regards.

---
Naoya Anzai

--
Adrian Klaver
adrian.klaver@gmail.com

Naoya Anzai <anzai-naoya@mxu.nes.nec.co.jp> writes:

Accroding to http://www.postgresql.org/docs/9.1/interactive/sql-createtableas.html ,
"CREATE TABLE table_name AS EXECUTE name WITH DATA" seems a right syntax,
but,this statement becomes a SYNTAX ERROR.

Hmm ... it looks like WITH [NO] DATA is actually only implemented for
the query = SelectStmt case, not the query = ExecuteStmt case. We need
a less klugy implementation to support EXECUTE :-(

regards, tom lane