0

I have a very simple function that accepts _posttypeid and _url as parameters:

CREATE FUNCTION rewrite(_postid integer DEFAULT NULL::integer,
                                       _url character varying DEFAULT NULL::character varying)
    RETURNS TABLE
            (
                DestinationURL             varchar,
            )
    LANGUAGE plpgsql
AS
$function$
BEGIN
RETURN QUERY
                SELECT 
                NULL AS PostTypeID,
                _url      AS DestinationURL,
                      
                FROM reference.destinations dest1
            
                WHERE length(TRIM(dest1.DestinationURL)) > 0
                AND _url LIKE '%' + TRIM(dest1.DestinationURL)) + '%'
                ORDER BY length(dest1.DestinationURL)) DESC
                LIMIT 1;
END;
$function$

If I run select * from public.rewrite(_url := 'wikipedia.org') then I get this error:

[42883] ERROR: operator does not exist: unknown + text

Hint: No operator matches the given name and argument types. You might need to add explicit type casts.

I am clearly passing in text as the value for the _url paramater. I don't understand what the error means or how to go about fixing it.

volume one
  • 6,800
  • 13
  • 67
  • 146
  • 3
    The string [concatenation operator](https://www.postgresql.org/docs/current/functions-string.html) in SQL is `||`. The `+` is only to add numbers –  Jan 12 '21 at 12:59
  • 1
    It's not a valid plpgsql function, BEGIN and END are missing. – Frank Heikens Jan 12 '21 at 13:01
  • @FrankHeikens You're right - it was a copy paste error. added now. – volume one Jan 12 '21 at 13:11
  • @a_horse_with_no_name well that just fixed it up nicely! didnt realise migrating a database from SQL Server to Postgresql was going to be this hard – volume one Jan 12 '21 at 13:21
  • 1
    Well, Microsoft doesn't care about the SQL standard, while the Postgres devs do - so Postgres follows the SQL standard wherever possible (especially for things defined over 30 years ago like the `||` operator) –  Jan 12 '21 at 13:29
  • @a_horse_with_no_name is there any reference/docs on the standard SQL language? – volume one Jan 12 '21 at 13:32

0 Answers0