postgres query has no destination for result data

Question

I'm very new to pgSQL.

Can any one help with resolving this 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 prac_data.document_get_document_title(integer) line 8 at SQL statement

The script is...

CREATE OR REPLACE FUNCTION prac_data.document_get_document_title(documentid INTEGER) RETURNS TEXT
AS
$body$
DECLARE
xmlText XML;
rawText TEXT;
BEGIN
    SELECT document into STRICT rawText FROM prac_data.document_table WHERE entity_id = documentid;
    xmlText := XMLParse (DOCUMENT rawText);            
    SELECT xpath('/inps_flow_document/@title', xmlText) as titleText;
    RETURN titleText;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
            RAISE EXCEPTION 'document_id % not found', documentid;
END;
$body$ LANGUAGE PLPGSQL;


SELECT prac_data.document_get_document_title(1)

im using SELECT into (as can been seen), but perhaps I'm not using it quite correctly....

try changing this SELECT xpath('/inps_flow_document/@title', xmlText) as titleText; to SELECT xpath('/inps_flow_document/@title', xmlText) into yourVariable as titleText; — Satya
– Satya, Commented Jun 24, 2014 at 17:24

Ihor Romanchenko · Accepted Answer · 2014-06-24 17:51:11Z

2

Try something like:

CREATE OR REPLACE FUNCTION prac_data.document_get_document_title(documentid INTEGER) RETURNS TEXT
AS
$body$
DECLARE
xmlText XML;
rawText TEXT;
titleText TEXT;
BEGIN
    SELECT document into STRICT rawText FROM prac_data.document_table WHERE entity_id = documentid;
    xmlText := XMLParse (DOCUMENT rawText);            
    titleText := xpath('/inps_flow_document/@title', xmlText);
    RETURN titleText;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
            RAISE EXCEPTION 'document_id % not found', documentid;
END;
$body$ LANGUAGE PLPGSQL;

answered Jun 24, 2014 at 17:51

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

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

Collectives™ on Stack Overflow

postgres query has no destination for result data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related