PostgreSQL CREATE TEMPORARY TABLE inside a plpgsql function

Question

I am trying to create a function that does this:

drop table t_rv_openitem;
select * into t_rv_openitem from rv_openitem;
select * from t_rv_openitem;

I am confused sometimes when it comes to functions in PostgreSQL and get this error:

An error has occurred:

ERROR: syntax error at or near "DROP" LINE 3: DROP TABLE t_rv_openitem;

I know this seems like a simple task but I am pulling my hair out trying to figure this out.

Here is the full function create statement:

CREATE OR REPLACE FUNCTION adempiere.update_t_rv_openitem()
  RETURNS rv_openitem AS
$BODY$

Drop table t_rv_openitem;
select * into t_rv_openitem from rv_openitem;
select * From t_rv_openitem;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION adempiere.update_t_rv_openitem() OWNER TO adempiere;

I'm sorry, I don't understand above what? I've added the entire function create statement, does that help. — Dan Walmsley
– Dan Walmsley, Commented Apr 11, 2012 at 3:16

Michael Buen · Accepted Answer · 2012-04-11 03:51:09Z

4

Just add BEGIN and END

CREATE OR REPLACE FUNCTION adempiere.update_t_rv_openitem()
  RETURNS rv_openitem AS
$BODY$

BEGIN -- ADD THIS

Drop table t_rv_openitem;
select * into t_rv_openitem from rv_openitem;
select * From t_rv_openitem;

END; -- AND THIS

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION adempiere.update_t_rv_openitem() OWNER TO adempiere;

You don't need BEGIN and END block if you are using LANGUAGE sql, you need those though if you are using LANGUAGE plpgsql

UPDATE

Regarding ERROR: syntax error at "t_rv_openitem" DETAIL: Expected record variable.... There's no syntax error on your code, you just need to change this:

select * into t_rv_openitem from rv_openitem;

To this:

create table t_rv_openitem as
select * from rv_openitem;

The table creation using SELECT * INTO tablehere FROM tableSource works only if you are using it outside of PLPGSQL; when that code struct is inside PLPGSQL, the semantic will be different, it means:

SELECT * INTO declaredVariableHere FROM tableSource;

To make table creation work on both stand-alone statement and inside PLPGSQL, just use:

CREATE TABLE AS SELECT * FROM tableSourceHere;

edited Apr 11, 2012 at 3:51

answered Apr 11, 2012 at 3:21

Michael Buen

39.6k10 gold badges99 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dan Walmsley Over a year ago

Thanks, but now I get this ERROR: syntax error at "t_rv_openitem" DETAIL: Expected record variable, row variable, or list of scalar variables following INTO. CONTEXT: compilation of PL/pgSQL function "update_t_rv_openitem" near line 5 ********** Error ********** ERROR: syntax error at "t_rv_openitem" SQL state: 42601 Detail: Expected record variable, row variable, or list of scalar variables following INTO. Context: compilation of PL/pgSQL function "update_t_rv_openitem" near line 5

Michael Buen Over a year ago

True it(SELECT * INTO newTable FROM sourceTable) does not work in plpgsql, but it does work when it is run as stand-alone statement. That confuses me also before, same syntax yet it will have different semantic when it is placed inside the procedural language

Erwin Brandstetter Over a year ago

@DanWalmsley: SELECT INTO is used for this purpose in plpgsql

Collectives™ on Stack Overflow

PostgreSQL CREATE TEMPORARY TABLE inside a plpgsql function

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related