Lists: | pgsql-general |
---|
From: | Naoya Anzai <anzai-naoya(at)mxu(dot)nes(dot)nec(dot)co(dot)jp> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | Akio Iwaasa <iwaasa(at)mxs(dot)nes(dot)nec(dot)co(dot)jp> |
Subject: | "CREATE TABLE table_name AS EXECUTE name WITH DATA" becomes syntax error. |
Date: | 2011-11-24 09:55:53 |
Message-ID: | 52CCAA8F414BA8anzai-naoya@mxu.nes.nec.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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;
---
Regards.
---
Naoya Anzai
From: | Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Cc: | Naoya Anzai <anzai-naoya(at)mxu(dot)nes(dot)nec(dot)co(dot)jp>, Akio Iwaasa <iwaasa(at)mxs(dot)nes(dot)nec(dot)co(dot)jp> |
Subject: | Re: "CREATE TABLE table_name AS EXECUTE name WITH DATA" becomes syntax error. |
Date: | 2011-11-24 17:13:29 |
Message-ID: | 201111240913.29819.adrian.klaver@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(dot)klaver(at)gmail(dot)com
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Naoya Anzai <anzai-naoya(at)mxu(dot)nes(dot)nec(dot)co(dot)jp> |
Cc: | pgsql-general(at)postgresql(dot)org, Akio Iwaasa <iwaasa(at)mxs(dot)nes(dot)nec(dot)co(dot)jp> |
Subject: | Re: "CREATE TABLE table_name AS EXECUTE name WITH DATA" becomes syntax error. |
Date: | 2011-11-24 18:48:57 |
Message-ID: | 26753.1322160537@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Naoya Anzai <anzai-naoya(at)mxu(dot)nes(dot)nec(dot)co(dot)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