Temporary tables

[pgnovice]

Can you build a temporary table with in a sql function?

Ex:

CREATE FUNCTION fnTest(integer) RETURNS bigint AS'
 create temporary table mfb (business_name varchar(50) not null);
 
 insert into mfb
  select business_prov_name from mco61_provider
  where npi = $1;
 
 select count(1) from mfb;
 '
 LANGUAGE SQL;
 

I'm getting an err

ERROR: relation "mfb" does not exist
SQL state: 42P01
Context: SQL function "fntest"

[michaelb]

Can you build a temporary table with in a sql function?

Yes, you can, but this won't help you because you cannot have a function which makes use of the table it creates - the table must exist in order to create/compile this function.

Please use the CODE tags in your next posting, refer to Posting Guidelines at the top of the forum if you are not sure how to do it.