CODEWITHSUNDEEP
sqlpostgresqlplpgsqlpostgresql-9.1
Hans
Hans

Reputation: 2852

Postgresql: Return Temp Table From a Stored Procedure

I want to return the result of a query stored in a temp table from a stored procedure. I'm not sure what should be the last statement. Any help is appriciated:

CREATE OR REPLACE FUNCTION public.get_generic_info(
    IN in_app_id character varying,
    IN key_id uuid)
RETURNS TABLE(info text, last_update timestamp with time zone) AS
$BODY$  
BEGIN
    CREATE TEMP TABLE result AS
        SELECT  generic_info.info::text, generic_info.last_update FROM 
                generic_info 
        WHERE generic_info.app_id = in_app_id AND generic_info.id = key_id;

    -- some data manipulations based on 'result' rows       
    RETURN ???????;
END;

Upvotes: 1

Views: 1386

Answers (1)

cableload
cableload

Reputation: 4385

Your last statement would be like this

RETURN QUERY 
     SELECT  result.info::text, result.last_update FROM 
      result;

If you are not using TEMP table for anything else, there is no need to create a temp table at all..You can directly do 

RETURN QUERY
SELECT  generic_info.info::text, generic_info.last_update FROM 
                generic_info 
        WHERE generic_info.app_id = in_app_id AND generic_info.id = key_id;

Upvotes: 2

Related Questions