Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2014
    Posts
    1

    Post Unanswered: Unlike MySQL and SQL Server, no compilation is required to build an aggregate functio

    Unlike MySQL and SQL Server, no compilation is required to build an aggregate function in PostgreSQL.

    What is the meaning of this statement ? Can someone please breif with respect to SQL Server.

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    It means that you can write your aggregate function in pure SQL, and no separate code/compilation (and subsequent external dependency) is needed. In SQL Server, while you can write a function in T-SQL, you would need to write an aggregate function using visual studio and compile it before it could be used. Likewise, with mysql, you would have to write the function in C and compile. In both databases, this introduces an external dependency to the database.

    But, with PostgreSQL, you can write aggregate functions in pure SQL.

    For example, here's an example of SQL to create 'first' and 'last' aggregate functions (returns the first (or last) record's data for the selected field from the set of candidate records) :

    Code:
    CREATE OR REPLACE FUNCTION first_agg(anyelement, anyelement)
      RETURNS anyelement AS
    $BODY$
            SELECT $1;
    $BODY$
      LANGUAGE sql IMMUTABLE STRICT
      COST 100;
    ALTER FUNCTION first_agg(anyelement, anyelement)
      OWNER TO postgres;
    GRANT EXECUTE ON FUNCTION first_agg(anyelement, anyelement) TO public;
    GRANT EXECUTE ON FUNCTION first_agg(anyelement, anyelement) TO postgres;
    
    CREATE OR REPLACE FUNCTION last_agg(anyelement, anyelement)
      RETURNS anyelement AS
    $BODY$
            SELECT $2;
    $BODY$
      LANGUAGE sql IMMUTABLE STRICT
      COST 100;
    ALTER FUNCTION last_agg(anyelement, anyelement)
      OWNER TO postgres;
    GRANT EXECUTE ON FUNCTION last_agg(anyelement, anyelement) TO public;
    GRANT EXECUTE ON FUNCTION last_agg(anyelement, anyelement) TO postgres;
    
    -- ****************
    CREATE AGGREGATE first(anyelement) (
      SFUNC=first_agg,
      STYPE=anyelement
    );
    ALTER AGGREGATE first(anyelement)
      OWNER TO postgres;
    
    CREATE AGGREGATE last(anyelement) (
      SFUNC=last_agg,
      STYPE=anyelement
    );
    ALTER AGGREGATE last(anyelement)
      OWNER TO postgres;
    Now, to be clear, you CAN write an aggregate function in C or another compiled language for PostgreSQL, if necessary, as the compiled language version is likely to be faster than the SQL version. But, it may not be necessary to go to time and trouble to do so. It also allows you to write a quick aggregate function to satisfy an immediate need, and follow up later, if it is found to be needed for performance, with an externally compiled function.
    Last edited by loquin; 08-26-14 at 16:50. Reason: clarification
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Tags for this Thread

Posting Permissions

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