1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

PostgreSQL into MySQL

Discussion in 'PostgreSQL' started by Rama, Dec 22, 2008.

  1. Rama

    Rama New Member

    Joined:
    Dec 22, 2008
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    Hi there,

    I need to convert the following code from PostgreSQL into MySQL
    Code:
     
    [COLOR=darkred]CREATE TYPE type_name AS[/COLOR]
    [COLOR=darkred]([/COLOR]
    [COLOR=darkred]field_id INTEGER,[/COLOR]
    [COLOR=darkred]field_name VARCHAR(50)[/COLOR]
    [COLOR=darkred]);[/COLOR]
    [COLOR=darkred][/COLOR] 
    [COLOR=darkred][/COLOR] 
    [COLOR=darkred]CREATE FUNCTION function_name()[/COLOR]
    [COLOR=darkred]RETURNS SETOF type_name LANGUAGE plpgsql AS $$[/COLOR]
    [COLOR=darkred]  DECLARE[/COLOR]
    [COLOR=darkred]    variable_name type_name;[/COLOR]
    [COLOR=darkred]  BEGIN[/COLOR]
    [COLOR=darkred]    FOR variable_name IN[/COLOR]
    [COLOR=darkred]      SELECT field_id, field_name[/COLOR]
    [COLOR=darkred]      FROM table_name[/COLOR]
    [COLOR=darkred]      ORDER BY field_id[/COLOR]
    [COLOR=darkred]    LOOP[/COLOR]
    [COLOR=darkred]      RETURN NEXT variable_name;[/COLOR]
    [COLOR=darkred]    END LOOP;[/COLOR]
    [COLOR=darkred]  END;[/COLOR]
    [COLOR=darkred]$$;[/COLOR]
    [COLOR=darkred][/COLOR] 
    [COLOR=darkred][/COLOR] 
    [COLOR=darkred]SELECT * FROM function_name();[/COLOR]
    
    Can anybody help?

    Thanks in advance!
     
  2. pete_bisby

    pete_bisby New Member

    Joined:
    Nov 11, 2007
    Messages:
    48
    Likes Received:
    2
    Trophy Points:
    0
    Occupation:
    Software Developer/Analyst, PB
    Location:
    Manchester, England
    Home Page:
  3. Rama

    Rama New Member

    Joined:
    Dec 22, 2008
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    Thanks, but I found an alternative by creating mySQL procedures instead of functions.

    Code:

    CREATE PROCEDURE procedure_name()
    BEGIN
    SELECT fiend_id, field_name FROM table_name ORDER BY field_id;
    END$$


    Thanks anyway!
     
  4. Rama

    Rama New Member

    Joined:
    Dec 22, 2008
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    ...and called it this way:

    $sql = 'CALL procedure_name()';
     

Share This Page