PostgreSQL CREATE TEMPORARY TABLE inside a plpgsql function

plpgsql, postgresql, temp-tables

Solution

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;

Problem

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; ```

Original source