PostgreSQL - ERROR: query has no destination for result data

Question

Why am I getting the following error?:

ERROR: query has no destination for result data

This is my function:

CREATE OR REPLACE FUNCTION public.SumASCII(
    value character varying)
    RETURNS int
    LANGUAGE 'plpgsql'
    COST 100.0
    VOLATILE NOT LEAKPROOF 
AS $function$

    DECLARE finalResult int;
    DECLARE tempChar character;
    DECLARE valueLength int;
    DECLARE tempResult int;

    BEGIN
        SELECT LENGTH(value) INTO valueLength;
        SELECT finalResult = 0;
        SELECT tempResult = 0;

        DO
        $do$
        BEGIN 
        FOR i IN 1..valueLength LOOP
           SELECT SUBSTRING(value, i, 1) INTO tempChar;
           SELECT ASCII(tempChar) INTO tempResult;
           SELECT finalResult += tempResult;
        END LOOP;
        END
        $do$;

        RETURN finalResult;
    END;

$function$;

I've looked at other questions with the same error, but they don't seem to be related to my problem. I'm sure the answer is simple, but I just can't seem to see what the issue is here.. I'm declaring an int and I am returning an int..

Calling the function as follows:

SELECT SumASCII('abc')

using DO statement inside plpgsql is nonsense. Newer use SELECT instead assign statement :=. — Pavel Stehule
– Pavel Stehule, Commented Feb 10, 2017 at 4:35
The selects are completely unnecessary: postgresql.org/docs/current/static/… — user330315
– user330315, Commented Feb 10, 2017 at 6:53

mike.k · Accepted Answer · 2017-02-10 03:30:10Z

1

I was able to get it working by simplifying a lot. I believe DECLARE just needs to be used once (examples at https://www.postgresql.org/docs/9.6/static/plpgsql-declarations.html), and I'm not sure what you were trying to do with the DO...END block but I just took it out.

CREATE OR REPLACE FUNCTION public.SumASCII(
    value character varying)
    RETURNS int
    LANGUAGE 'plpgsql'
    COST 100.0
    VOLATILE NOT LEAKPROOF 
AS $function$

DECLARE
    finalResult int := 0;
BEGIN
    FOR i IN 1..LENGTH(value) LOOP
       finalResult := finalResult + ASCII(SUBSTRING(value, i, 1));
    END LOOP;
    RETURN finalResult;
END;
$function$;

SELECT SumASCII('abc') is returning 294

answered Feb 10, 2017 at 3:30

mike.k

3,4771 gold badge14 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user330315 Over a year ago

The language name is an identifier, it should not be quoted with single quotes: language plpgsql is correct (the single quotes are only accepted for backward compatibility)

mike.k Over a year ago

@a_horse_with_no_name, good eye. I skipped over the CREATE portion and went for the body.

Pavel Stehule · Accepted Answer · 2017-02-10 05:09:41Z

1

The Fortran style is style in PLpgSQL is bad style - any expression is SELECT. More embedded SELECTs, more slow. What can be done by simple one SQL should be done by simple SQL (it is not true in 100% cases (can depends on individual expressions) - for example @mike.k code is 3x faster than my: there is only one simple expression in cycle, my code has one generic query and very slow regexpr function):

CREATE OR REPLACE FUNCTION public.sumascii(varchar)
RETURNS bigint AS $$
  SELECT sum(ascii(c)) FROM regexp_split_to_table($1,'') g(c);
$$ LANGUAGE SQL IMMUTABLE;

The identifier in SQL (and PostgreSQL too) are not case sensitive, so is not good to use camel notation.

When the result of function is immutable in time (for given argument), then the function should be marked as IMMUTABLE.

edited Feb 10, 2017 at 5:09

answered Feb 10, 2017 at 4:44

Pavel Stehule

46.7k6 gold badges103 silver badges102 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - ERROR: query has no destination for result data

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related