2 Clean up
source | link

postgres function - declare Declare variable with selectfor SELECT statement in Postgres function

iI have modifedmodified my function, but iI have problems with declaredeclaring variables. I used postgresuse Postgres 8.4. Have someone an idea?

    CREATE OR REPLACE FUNCTION requestcounterid(_mindate timestamptz, _maxdate timestamptz) 
      RETURNS TABLE (kategorien text, requestcounter int) AS
    $func$  
    DECLARE
    _minid bigint; 
    _maxid bigint; 

    BEGIN 

SELECT id  INTO _minid from tablename  
where starttime >= $1 ORDER BY starttime ASC LIMIT 1; 
SELECT id  INTO _maxid from tablename   
where starttime < $2 ORDER BY starttime DESC LIMIT 1; 

    SELECT CASE WHEN duration <= 10000000 THEN '00-01 sec'::text
                WHEN duration <= 40000000 THEN '01-04 sec'
                WHEN duration <= 100000000 THEN '04-10 sec' 
                WHEN duration <= 300000000 THEN '10-30 sec' 
                WHEN duration <= 600000000 THEN '30-60 sec' 
                ELSE 'more than 60 sec' END  
         , count(*)::int                     
    FROM   tablename
    WHERE  id >= _minid and id <= _maxid
    GROUP  BY 1                              
    ORDER  BY 1; 

    END; 
    $func$ LANGUAGE plpgsql; 

Error:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "requestcounterid" line 12 at SQL statement
ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "requestcounterid" line 12 at SQL statement

How to fix this?

postgres function - declare variable with select statement

i have modifed my function, but i have problems with declare variables. I used postgres 8.4. Have someone an idea?

    CREATE OR REPLACE FUNCTION requestcounterid(_mindate timestamptz, _maxdate timestamptz) 
      RETURNS TABLE (kategorien text, requestcounter int) AS
    $func$  
    DECLARE
    _minid bigint; 
    _maxid bigint; 

    BEGIN 

SELECT id  INTO _minid from tablename where starttime >= $1 ORDER BY starttime ASC LIMIT 1; 
SELECT id  INTO _maxid from tablename  where starttime < $2 ORDER BY starttime DESC LIMIT 1; 

    SELECT CASE WHEN duration <= 10000000 THEN '00-01 sec'::text
                WHEN duration <= 40000000 THEN '01-04 sec'
                WHEN duration <= 100000000 THEN '04-10 sec' 
                WHEN duration <= 300000000 THEN '10-30 sec' 
                WHEN duration <= 600000000 THEN '30-60 sec' 
                ELSE 'more than 60 sec' END  
         , count(*)::int                     
    FROM   tablename
    WHERE  id >= _minid and id <= _maxid
    GROUP  BY 1                              
    ORDER  BY 1; 

    END; 
    $func$ LANGUAGE plpgsql; 

Error:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "requestcounterid" line 12 at SQL statement

Declare variable for SELECT statement in Postgres function

I have modified my function, but I have problems with declaring variables. I use Postgres 8.4.

CREATE OR REPLACE FUNCTION requestcounterid(_mindate timestamptz, _maxdate timestamptz) 
  RETURNS TABLE (kategorien text, requestcounter int) AS
$func$  
DECLARE
_minid bigint;
_maxid bigint;

BEGIN 

SELECT id  INTO _minid from tablename 
where starttime >= $1 ORDER BY starttime ASC LIMIT 1; 
SELECT id  INTO _maxid from tablename 
where starttime < $2 ORDER BY starttime DESC LIMIT 1; 

SELECT CASE WHEN duration <= 10000000 THEN '00-01 sec'::text
            WHEN duration <= 40000000 THEN '01-04 sec'
            WHEN duration <= 100000000 THEN '04-10 sec' 
            WHEN duration <= 300000000 THEN '10-30 sec' 
            WHEN duration <= 600000000 THEN '30-60 sec' 
            ELSE 'more than 60 sec' END  
     , count(*)::int                     
FROM   tablename
WHERE  id >= _minid and id <= _maxid
GROUP  BY 1                              
ORDER  BY 1; 

END; 
$func$ LANGUAGE plpgsql;

Error:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "requestcounterid" line 12 at SQL statement

How to fix this?

1
source | link

postgres function - declare variable with select statement

i have modifed my function, but i have problems with declare variables. I used postgres 8.4. Have someone an idea?

    CREATE OR REPLACE FUNCTION requestcounterid(_mindate timestamptz, _maxdate timestamptz) 
      RETURNS TABLE (kategorien text, requestcounter int) AS
    $func$  
    DECLARE
    _minid bigint; 
    _maxid bigint; 

    BEGIN 

SELECT id  INTO _minid from tablename where starttime >= $1 ORDER BY starttime ASC LIMIT 1; 
SELECT id  INTO _maxid from tablename  where starttime < $2 ORDER BY starttime DESC LIMIT 1; 

    SELECT CASE WHEN duration <= 10000000 THEN '00-01 sec'::text
                WHEN duration <= 40000000 THEN '01-04 sec'
                WHEN duration <= 100000000 THEN '04-10 sec' 
                WHEN duration <= 300000000 THEN '10-30 sec' 
                WHEN duration <= 600000000 THEN '30-60 sec' 
                ELSE 'more than 60 sec' END  
         , count(*)::int                     
    FROM   tablename
    WHERE  id >= _minid and id <= _maxid
    GROUP  BY 1                              
    ORDER  BY 1; 

    END; 
    $func$ LANGUAGE plpgsql; 

Error:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "requestcounterid" line 12 at SQL statement