Postgresql – Declare variable for SELECT statement in Postgres function

functionspostgresqlpostgresql-8.4

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?

Best Answer

If the SELECT with the CASE expression is something you want to return in the output table, just add RETURN QUERY before it:

RETURN QUERY SELECT CASE ...

Note: 8.4 is very old now. Even 9.0 is out of support by now - consider upgrading to a recent major version soon. The old ones usually don't get any security (and other) fixes anymore.

Related Solutions

Postgresql – Custom Postgres function not working

Peter's quite right about the problem, of course. However, this entire function is unnecessarily slow and complex - that repeated string concatenation will be horrible for performance, and PL/PgSQL loops are best avoided when you can anyway.

You can do the same job with an ordinary SQL function using generate_subscripts:

CREATE OR REPLACE FUNCTION intarr_to_hex_string(integer[]) RETURNS text AS $$
SELECT 
  string_agg(
    lpad(to_hex($1[x]),2,'0'),
    ''
    order by x
  ) 
FROM generate_subscripts($1,1) x;
$$ LANGUAGE sql;

The above version expects an integer array, but you can accept text arrays with an overload:

CREATE OR REPLACE FUNCTION intarr_to_hex_string(text[]) RETURNS text AS $$
SELECT intarr_to_hex_string($1::integer[])
$$ LANGUAGE sql;

Note that no sanity check is done to make sure the hex string is two digits or less. You should really add a check where you use a CASE statement to execute a PL/PgSQL function that raises an error if there's bad input. Here's a complete example:

CREATE OR REPLACE FUNCTION raise_exception(text)
  RETURNS void LANGUAGE plpgsql AS
$BODY$ 
BEGIN 
   RAISE EXCEPTION '%', $1; 
END; 
$BODY$;

CREATE OR REPLACE FUNCTION intarr_to_hex_string(integer[]) RETURNS text AS $$
SELECT 
  string_agg(lpad(
    CASE WHEN $1[x] < 256 THEN
      to_hex($1[x])
    ELSE
      raise_exception('Argument to intarr_to_hex_string contained value '||coalesce($1[x]::text,'NULL')||', expected all values in i[] to be 0 <= i < 256. Full argument was '||quote_literal($1)||'.')::text
    END,
    2, '0'),
  ''
  order by x) 
FROM generate_subscripts($1,1) x;
$$ LANGUAGE sql;

Postgresql – use function arguments or variable in postgres function

I have found the solution by using function triggers. I didn't dynamically change the default value but I used trigger function to update the column value before insert action. It can achieve the same result.

Create or replace function update_value() 
Returns Trigger AS $$
BEGIN
IF NEW.floor ISNULL THEN
    NEW.floor :=(select floor from buildings where my_current_location='yes');
END IF;

IF NEW.building ISNULL THEN                                         
    NEW.building :=(select name from buildings my_current_location='yes');      
END IF;
RETURN NEW;
END $$ Language 'plpgsql';

Also you need to add a trigger to the table:

Create triggers update_value_trigger Before Insert On points For each row execute procedure update_value();

Best Answer

Related Solutions

Postgresql – Custom Postgres function not working

Postgresql – use function arguments or variable in postgres function

Related Question