Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2011
    Posts
    13

    Smile Unanswered: Function to generate value on the fly

    Hi,

    This is my postgresql functuion that I created using help on stackoverflow:

    Code:
    -- Setup Solrid Function
    CREATE OR REPLACE FUNCTION solrid(IN local_id INTEGER, OUT result TEXT) AS $$
    DECLARE
        database_id TEXT := 'A';
    BEGIN
        result := database_id || local_id::TEXT;
    END;
     $$ LANGUAGE PLPGSQL;
    Now I am moving my database to Mysql and I can't find out how to convert this function from postgresql to mysql. I have found two different things:

    First option:
    Code:
    CREATE OR REPLACE FUNCTION solrid(integer) RETURNS text AS $$ SELECT 'A'||$1; $$ LANGUAGE SQL
    Second option:
    Code:
    DELIMITER //
    DROP FUNCTION IF EXISTS solrid //
    CREATE FUNCTION solrid(local_id INTEGER) RETURNS TEXT
    BEGIN
      DECLARE database_id TEXT;
      SET database_id = 'A';
      RETURN CONCAT(database_id, CAST(local_id AS CHAR));
    END //
    DELIMITER ;
    Using Sqlfiddle I can't get either one to work as I want it to, or even just not throw out syntax errors. What I am trying to do is take the "id" from the "job" table and stick a letter on the front, in this case "A" to give me a "solrid" which is geenrated on the fly and not stored. Here is an example:

    Job Table (id): 123
    Solrid: A123

    Can anyone help me on this please? I'm not very good with databases and feel a bit out of my depth.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    You were very close. Try the following:

    Code:
    mysql> DELIMITER //
    mysql> DROP FUNCTION IF EXISTS solrid //
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> CREATE FUNCTION solrid(local_id INTEGER) RETURNS TEXT
        -> DETERMINISTIC
        -> BEGIN
        ->   DECLARE database_id TEXT;
        ->   SET database_id := 'A';
        ->   RETURN CONCAT(database_id, CAST(local_id AS CHAR));
        -> END //
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> DELIMITER ;
    mysql> select solrid(543);
    +-------------+
    | solrid(543) |
    +-------------+
    | A543        | 
    +-------------+
    1 row in set (0.00 sec)
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •