Thread: CTE error

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    7
    Rep Power
    0

    Question CTE error


    Hi,

    Please forgive I am very new to Postgres I am trying to write a function that will return a string. Here is what I have so far:

    Code:
    CREATE OR REPLACE FUNCTION fn_form_app_relative_url_from_page_base_id(page_id_param integer) RETURNS TEXT AS $BODY$ DECLARE result text := 'Tawreet'; BEGIN WITH pages(id, parent_id ,url, is_homepage) AS ( SELECT pb.id, pb.parent_id, Cast(pb.file_name as VARCHAR(250)), is_home_page FROM tbl_page_base pb WHERE pb.Id = page_id_param ) SELECT * FROM pages;--1 ? --result := pages.url;--2 ? return result; END; $BODY$ LANGUAGE plpgsql IMMUTABLE COST 100; ALTER FUNCTION fn_form_app_relative_url_from_page_base_id(integer) OWNER TO postgres;
    When I run it I get:
    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 fn_form_app_relative_url_from_page_base_id(integer) line 13 at SQL statement

    If I execute the SQL inside the WITH statement the SQL is valid.

    Any ideas are welcome please.

    Thanks, Dave.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    7
    Rep Power
    0
    Sorry I worked it out it should have selected like this: SELECT url FROM pages INTO result;

IMN logo majestic logo threadwatch logo seochat tools logo