Postgresql – SQL: How to create a table inside of a function

postgresql

For some reason, whenever I attempt to create tables and drop them within an SQL function, I'm met with a syntax error. When I do them outside it's always fine but inside the function it becomes a problem.

I've truncated my code significantly but this is similar to what I have:

DECLARE
  
CREATE TABLE inputCategories(
      Category varchar(255)
   );

DROP TABLE inputCategories;

return 0
END ;
$$ LANGUAGE plpgsql ;

I am required to do all table operations within this function that will be run several times, any help is greatly appreciated, thanks in advance!

Best Answer

there is semi colon missing after RETURN 0 ... and also no BEGIN ... ... so a slight improvement, with no syntax error, would be :

CREATE FUNCTION _name_ ()
RETURNS
integer
AS
$$
BEGIN
  
CREATE TABLE inputCategories(
      Category varchar(255)
   );

DROP TABLE inputCategories;

RETURN 0 ;

END ;
$$ LANGUAGE plpgsql
   VOLATILE ;

(( VOLATILE indicates to postgres that the function could change the database !! It is the default for a function definition, so there is no need to write it, although it helps to put it in for clarity. VOLATILE also means that function 'can do anything'1, for example the returned result from the function could be different after each call (but not in this simple case) and so a VOLATILE function will be evaluated at each point in a query where it is called. In this case the function is VOLATILE because it has side effects in the database ... it creates and then drops a table. ))

postgres docs : Function Volatility Categories

Another way to define that function would be as an SQL language function :

CREATE FUNCTION _name_ ()
RETURNS
integer
AS
$$
  
CREATE TABLE inputCategories(
      Category varchar(255)
   );

DROP TABLE inputCategories;

SELECT 0;

$$
LANGUAGE SQL
VOLATILE ;

SQL language functions and PLPGSQL language functions are treated differently, and so depending on the nature of your real function, there may be an advantage to either way. If it is a more complex function requiring dynamically generated SQL, for example, then PLPGSQL is the way. Or some other supported langage like PL/Perl or PL/Python.

There is a discussion of the differences between PLPGSQL and SQL language functions (as well as a little about more recently implemented stored procedures) here :

dba.stackexchange.com : Function Performance

Related Question