CODEWITHSUNDEEP
sqlpostgresqlplpgsql
Reikryv
Reikryv

Reputation: 1

How to run a function without storing it postgresql

Sorry I'm pretty new on this i would like to run something like the code but without storing it, just output the data as i am a read only user in the database I want to run it. BTW i know that the code there is very simple. I just wanna know how to print the data as it is an example.

CREATE [OR REPLACE] FUNCTION a ()
RETURNS timestamp
LANGUAGE plpgsql   
AS $variable_name$     
DECLARE 
  time_a timestamp; 
BEGIN     
  SELECT facilitytime 
     INTO time_a 
  FROM metrics 
  ORDER BY facilitytime DESC 
  LIMIT 1;   

  RETURN time_a;
END;    
$$

Upvotes: 0

Views: 557

Answers (2)

Schwern
Schwern

Reputation: 165396

There is were i have a problem as i want to use variables and multiple selects.

SQL is a declarative language where you ask questions (queries) and get answers (results). Some SQL implementations support variables, but they don't fit the SQL paradigm well.

Instead, when you want to use the results of several queries, use sub-selects to turn multiple selects into a single query. These can be organized using a Common Table Expression (CTE) also known as with.

with first_time as (
  SELECT facilitytime
  FROM metrics 
  ORDER BY facilitytime DESC 
  LIMIT 1;   
)
select facilitytime 
from first_time 
limit 1;

An alternative is to select into a temporary table to store your results. Then you can do multiple queries.

-- This creates a temp table for you similar to: 
-- create temp table latest_facilitytime ( facilitytime timestamp );
SELECT facilitytime 
INTO TEMP latest_facilitytime
FROM metrics 
ORDER BY facilitytime DESC 
LIMIT 1; 

select facilitytime
from latest_facilitytime;

Demonstration

Upvotes: 0

lewis.michaelr
lewis.michaelr

Reputation: 1

DO functions cannot return data directly, but they can declare cursors and those can be accessed after the DO function ends.

Upvotes: 0

Related Questions