CODEWITHSUNDEEP
postgresqlplpgsql
Nulik
Nulik

Reputation: 7380

PLPGSQL error: query has no destination for result data on a parameter declaration

I have a weird Postgres error saying:

NOTICE:  create_task() failed with... error: 42601 query has no destination for result data
NOTICE:  context: >>PL/pgSQL function app.create_task(character varying,character varying,character varying,integer,integer,integer,character varying,character varying,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,boolean,integer,character varying,character varying,character varying) line 22 at SQL statement<<
result=undefined

and I don't understand why. Line 22 is :

email               varchar,

What does the 'email' (parameter declaration) has to do with destination for 'result data' ? It is not even a statement. Very weird. There error messages are clueless.

The function I am calling is declared as this:

CREATE OR REPLACE FUNCTION app.create_task(
    identity_id_str     varchar,
    session_str         varchar,
    company_id_str      varchar,
    parent_dep_id       integer,
    sender_id           integer,
    recipient_id        integer,
    title               varchar,
    description         varchar,
    template_id         integer,
    plan_start_date     integer,
    plan_end_date       integer,
    start_date          integer,
    end_date            integer,
    time2solve          integer,
    type_id             integer,
    priority_id         integer,
    status_id           integer,
    progress            integer,
    access              integer,
    is_template         boolean,
    udata_id            integer,
    full_name           varchar,
    email               varchar,
    notes               varchar
)
RETURNS integer as $$
DECLARE
    v_identity_id               bigint;
    v_session                   bigint;
    v_company_id                bigint;
    user_id                     integer;
    utype                       smallint;
    server_session              bigint;
    schema_name                 varchar;
    query                       varchar;
    generic                     boolean;
    current_ts                  integer;
    dep_id                      integer;
    _c                          text;
BEGIN
        SELECT extract(epoch from now())::integer into current_ts;
        SELECT identity_id_str::bigint INTO v_identity_id;
        SELECT session_str::bigint INTO v_session;
        SELECT company_id_str::bigint INTO v_company_id;
        schema_name:='comp' || company_id_str;

        select session from app.session where app.session.identity_id=v_identity_id and app.session.session=v_session;
        IF NOT FOUND
        THEN
            RAISE EXCEPTION 'Invalid session' USING ERRCODE = '-1';
        END IF;

        query:='SELECT utype FROM uzer,identity WHERE uzer.identity_id=identity.identity_id AND uzer.user_id=' || sender_id;
        EXECUTE QUERY query into utype;
        IF NOT FOUND
        THEN
            RAISE EXCEPTION 'User not found' USING ERRCODE = '-2';
        END IF;

        IF current_ts > start_date 
        THEN
            start_date:=current_ts;
        END IF;
        query:='SELECT dep_id FROM ' || schema_name || '.task WHERE dep_id=' || parent_dep_id ;
        PERFORM query;
        IF NOT FOUND
        THEN
            RAISE EXCEPTION 'Parent task was not found' USING ERRCODE = '-1';
        END IF;

        IF udata = 0 THEN
            query='INSERT INTO ' || schema_name || '.udata(user_id,email,full_name,notes) VALUES ('
                || user_id
                || ',''' || email 
                || ',''' || full_name
                || ''') returning udata_id';
            EXECUTE query into udata_id;
        END IF;

        query:='INSERT INTO ' || schema_name || '.task(template_id,creator_id,user_id,udata_id,num_inlinks,plan_start_date,plan_end_date,start_date,end_date,time2solve,type_id,priority_id,status_id,progress,access,title,description) VALUES('  
                || template_id
                || ',' || user_id 
                || ',' || user_id
                || ',' || udata_id
                || ',1' 
                || ',''''' 
                || ',''''' 
                || ',''''' 
                || ',''''',
                || ',''''',
                || ',' || time2solve
                || ',' || type_id
                || ',' || priority_id
                || ',' || status_id
                || ',' || progress
                || ',' || access
                || ',"' || title
                || '","' || description
                || '") returning dep_id';
        EXECUTE query INTO dep_id;
        query:='INSERT INTO ' || schema_name || '.link(from_dep_id,to_dep_id,action_id,link_type,sender_id,recipient_id) VALUES(' ||
                parent_dep_id || ',' || dep_id || ',2,' || user_id || ',' || recipient_id || ')';
        RAISE NOTICE 'q=%',query;
        PERFORM query;
        query:='INSERT INTO ' || schema_name || '.action(dep_id,sender_id,recipient_id,dateinserted,minutes,code,comment,description) VALUES(' ||
                dep_id || ',' || sender_id || ',' || recipient_id || ',' || start_date || ',0,2,1,''Task created'')';
        RAISE NOTICE 'q=%',query;
        PERFORM query;
        RETURN dep_id;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'create_task() failed with... error: % %',SQLSTATE,SQLERRM;
        GET STACKED DIAGNOSTICS _c = PG_EXCEPTION_CONTEXT;
        RAISE NOTICE 'context: >>%<<', _c;
END
$$ LANGUAGE plpgsql;

This is how I am calling it from a Node.js javascript file:

function create_task(web_socket,data) {
    var query='select app.create_task($1::varchar,$2::varchar,$3::varchar,$4::integer,$5::integer,$6::integer,$7::varchar,$8::varchar,$9::integer,$10::integer,$11::integer,$12::integer,$13::integer,$14::integer,$15::integer,$16::integer,$17::integer,$18::integer,$19::integer,$20::boolean,$21::integer,$22::varchar,$23::varchar,$24::varchar) as dep_id';
    pool.query(query,[
            data.params.identity_id_str,
            data.params.session_str,
            data.params.company_id_str,
            data.params.task_data.parent_dep_id,
            data.params.task_data.sender_id,
            data.params.task_data.recipient_id,
            data.params.task_data.title,
            data.params.task_data.description,
            data.params.task_data.template_id,
            data.params.task_data.plan_start_date,
            data.params.task_data.plan_end_date,
            data.params.task_data.start_date,
            data.params.task_data.end_date,
            data.params.task_data.time2solve,
            data.params.task_data.type_id,
            data.params.task_data.priority_id,
            data.params.task_data.status_id,
            data.params.task_data.progress,
            data.params.task_data.access,
            data.params.task_data.is_template,
            data.params.task_data.udata_id,
            data.params.task_data.full_name,
            data.params.task_data.email,
            data.params.task_data.notes
        ],function(err,result) {

            console.log('result=' + util.inspect(result) + '\n');
            console.log('error=|||||||' + util.inspect(err) + "||||||||\n");
            if (typeof err !== 'undefined') {
                if (err!==null) {
                    console.log('Error:[[[' + err + ']]]\n');
                    return;
                }
            }

            var dep_id=result.rows[0].dep_id;
            var out='{"cmd_code":' + data.cmd_code + ',"rkey":"' + data.rkey + '","params":{"err_code":0,"error_text":"","identity_id_str":"' + data.params.identity_id_str + '","company_id_str":"' + data.params.company_id_str + '","dep_id":' + dep_id + '}}';
            web_socket.send(out);
    });

And this is my debug output:

Received from client: {"cmd_code":6,"rkey":"9d07ac2ddb2b1d7f","params":{"identity_id_str":"8","session_str":"162616490852087","company_id_str":"24","task_data":{"parent_dep_id":0,"sender_email":0,"recipient_id":1,"title":"","description":"","template_id":0,"plan_start_date":0,"plan_end_date":0,"start_date":0,"end_date":0,"time2solve":0,"type_id":0,"priority_id":0,"status_id":0,"progress":0,"access":0,"is_template":false,"full_name":"","email:":"[email protected]","notes":""}}}
cmd_code=6
NOTICE:  create_task() failed with... error: 42601 query has no destination for result data
NOTICE:  context: >>PL/pgSQL function app.create_task(character varying,character varying,character varying,integer,integer,integer,character varying,character varying,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,boolean,integer,character varying,character varying,character varying) line 22 at SQL statement<<
result=undefined

error=|||||||{ Error: ERROR:  control reached end of function without RETURN
CONTEXT:  PL/pgSQL function app.create_task(character varying,character varying,character varying,integer,integer,integer,character varying,character varying,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,boolean,integer,character varying,character varying,character varying)

    at Client._readError (/usr/node/lib/node_modules/pg-native/index.js:80:13)
    at Client._read (/usr/node/lib/node_modules/pg-native/index.js:121:19)
    at emitNone (events.js:86:13)
    at PQ.emit (events.js:185:7)
  severity: 'ERROR',
  sqlState: '2F005',
  messagePrimary: 'control reached end of function without RETURN',
  context: 'PL/pgSQL function app.create_task(character varying,character varying,character varying,integer,integer,integer,character varying,character varying,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,boolean,integer,character varying,character varying,character varying)',
  sourceFile: 'pl_exec.c',
  sourceLine: '361',
  sourceFunction: 'plpgsql_exec_function' }||||||||

Error:[[[Error: ERROR:  control reached end of function without RETURN
CONTEXT:  PL/pgSQL function app.create_task(character varying,character varying,character varying,integer,integer,integer,character varying,character varying,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,integer,boolean,integer,character varying,character varying,character varying)
]]]

Upvotes: 2

Views: 5270

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658452

The line count for error messages starts at the begin of the function body, which is at the dollar quote here (including the line):

RETURNS integer as $$

Line 22 is in fact:

select session from app.session where app.session.identity_id=v_identity_id and app.session.session=v_session;

And that's the SELECT without destination right there. Would work with PERFORM instead of SELECT, but better use IF EXISTS instead:

IF EXISTS (SELECT 1
           FROM   app.session s
           WHERE  s.identity_id = v_identity_id
           AND    s.session = v_session
          ) THEN
   RAISE EXCEPTION 'Invalid session' USING ERRCODE = '-1';
END IF;

There are probably more problems. I stopped looking there.

Related, with more explanation:

Upvotes: 2

Pavel Stehule
Pavel Stehule

Reputation: 45910

There probably more issues than one. Some errors are consequences of previous errors.

You try to run dynamic SQL with PERFORM statement. It doesn't work

query := 'INSERT ...'
PERFORM query; -- it is wrong
EXECUTE query; -- only this is working

The PostgreSQL console psql has command \sf+ funcname. It shows line numbers and it can help to you identify problematic rows.

Upvotes: 1

Related Questions