PostgreSQL into MySQL

Rama's Avatar, Join Date: Dec 2008
Newbie Member
Hi there,

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

Thanks in advance!
0
pete_bisby's Avatar, Join Date: Nov 2007
Go4Expert Member
From what I can tell MySQL doesn't support Types like PostgreSQL.

However you can use the SET datatype. Have a look at the MySQL manual (http://dev.mysql.com/doc/refman/5.1/en/set.html) for more detail
0
Rama's Avatar, Join Date: Dec 2008
Newbie Member
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!
0
Rama's Avatar, Join Date: Dec 2008
Newbie Member
...and called it this way:

$sql = 'CALL procedure_name()';