Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Nov 2009
    Posts
    15

    Unanswered: Converting functions of Postgre SQL into SQL Server 2005

    I have to convert the Postgre SQL functions into SQL Server 2005 functions. I have already converted the database. But functions are not converted.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Good for you!
    So what's the question?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2009
    Posts
    15
    FYI, I have join the portal just now, I have posted there by mistake. I am extremely sorry for that.

    I have to convert the whole Postgre SQL database into SQL Server 2005. I have used a tool for it. Everything is converted except of functions. I do not know about Postgre SQL so I cannot understand its functions.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Again, you haven't asked a question.

    I suspect the best course is to post up one function at a time and tell us what it does. We can then do our best to come up with the equivalent.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2009
    Posts
    15
    I am a software developer, I have not developed this application. My client just asked me to move his sites from one hosting to another. New hosting server do not support Postgre SQL. So I have to convert the whole database into SQL Server and fix the code. I do not know the functions in depth. But I have to run whole application. This is the first function.

    -- Function: _get_parser_from_curcfg()

    -- DROP FUNCTION _get_parser_from_curcfg();

    CREATE OR REPLACE FUNCTION _get_parser_from_curcfg()
    RETURNS text AS
    ' select prs_name from pg_ts_cfg where oid = show_curcfg() '
    LANGUAGE 'sql' IMMUTABLE STRICT
    COST 100;
    ALTER FUNCTION _get_parser_from_curcfg() OWNER TO root;

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - user defined functions, not system functions.
    How many of these do you need to convert? Are there other objects (like views and stored procedures)? I do not know postgre either so although I can see more-or-less what this should be in T-SQL I cannot account for some of the Postgre commands (e.g. COST 100 etc).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you have lots to do then you might want to hire a contractor for a day or two to help you convert them.
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I think sadianaz is the contractor...
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    sub-contract the work
    George
    Home | Blog

  10. #10
    Join Date
    Nov 2009
    Posts
    15
    There are 48 functions and 7 sequences. Nothing else. All functions are of 4 to 5 lines.

  11. #11
    Join Date
    Nov 2009
    Posts
    15
    hahaha very funny. It is not such a critical issue. I have handled much more than this many times ...

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Are you familiar with T-SQL? If so, your posted function is simply a scalar that returns a string (I don't know exactly what 'text' is in postgre). However, it references another function - show_curcfg() - in the where predicate.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Nov 2009
    Posts
    15
    Quote Originally Posted by pootle flump View Post
    Are you familiar with T-SQL? If so, your posted function is simply a scalar that returns a string (I don't know exactly what 'text' is in postgre). However, it references another function - show_curcfg() - in the where predicate.
    No I have never worked on T-SQL. 'text' is a datatype in Postgre SQL and equivalent to Varchar(max). There is no user defined function with name 'show_curcfg()'.

  14. #14
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by sadianaz View Post
    There are 48 functions and 7 sequences. Nothing else. All functions are of 4 to 5 lines.
    Sequences will be hard to port to SQL Server. There is no equivalent concept there.
    If the sequences are only used to populate some ID column automatically you might get away with an identity column. But that behaves differently and needs some changes in the source code as well (e.g. when re-using a generated value)

    The above function simply returns the a result set based on the SELECT inside that function.

    The function can be used just like a view, e.g. SELECT * FROM _get_parser_from_curcfg().
    I believe SQL Server can do something similar.

  15. #15
    Join Date
    Nov 2009
    Posts
    15
    Ok, so you mean that I can use view for this purpose, here are some more functions.
    ------------------------------------------
    -- Function: akeys(hstore)

    -- DROP FUNCTION akeys(hstore);

    CREATE OR REPLACE FUNCTION akeys(hstore)
    RETURNS text[] AS
    '$libdir/hstore', 'akeys'
    LANGUAGE 'c' IMMUTABLE STRICT
    COST 1;
    ALTER FUNCTION akeys(hstore) OWNER TO root;
    ------------------------------------------------

    -- Function: avals(hstore)

    -- DROP FUNCTION avals(hstore);

    CREATE OR REPLACE FUNCTION avals(hstore)
    RETURNS text[] AS
    '$libdir/hstore', 'avals'
    LANGUAGE 'c' IMMUTABLE STRICT
    COST 1;
    ALTER FUNCTION avals(hstore) OWNER TO root;
    --------------------------------------------------------

    -- Function: defined(hstore, text)

    -- DROP FUNCTION defined(hstore, text);

    CREATE OR REPLACE FUNCTION defined(hstore, text)
    RETURNS boolean AS
    '$libdir/hstore', 'defined'
    LANGUAGE 'c' IMMUTABLE STRICT
    COST 1;
    ALTER FUNCTION defined(hstore, text) OWNER TO root;
    ---------------------------------------------------------

    -- Function: "delete"(hstore, text)

    -- DROP FUNCTION "delete"(hstore, text);

    CREATE OR REPLACE FUNCTION "delete"(hstore, text)
    RETURNS hstore AS
    '$libdir/hstore', 'delete'
    LANGUAGE 'c' IMMUTABLE STRICT
    COST 1;
    ALTER FUNCTION "delete"(hstore, text) OWNER TO root;
    ----------------------------------------------------------

    -- Function: difference(text, text)

    -- DROP FUNCTION difference(text, text);

    CREATE OR REPLACE FUNCTION difference(text, text)
    RETURNS integer AS
    '$libdir/fuzzystrmatch', 'difference'
    LANGUAGE 'c' IMMUTABLE STRICT
    COST 1;
    ALTER FUNCTION difference(text, text) OWNER TO root;

Posting Permissions

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