SitePoint Sponsor

User Tag List

Results 1 to 21 of 21
  1. #1
    Perl/Mason Guru Flawless_koder's Avatar
    Join Date
    Feb 2002
    Location
    Gatwick, UK
    Posts
    1,206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Postgres Functions - are they stored procedures

    My friend and i have been having a long winded debate, and i think the best idea is to post it here to get a response:

    Postgres has functions.

    The functions are written in SQL/C/PlpgSQL/etc.

    An example we have found states:

    The PL/pgSQL call handler parses the function's source text and produces an internal binary instruction tree the first time the function is called. The produced bytecode is identified in the call handler by the object ID of the function.

    Which indicates that the function is compiled and procedural, stored in the database... you get my drift.

    The other common language for functions is compiled c, with an external file.

    No where does Postgres refer to them as "stored procedures", which i think they must be.

    Mullie should reply to this straight away, so you can see his side... and tell me whether they are stored procedures or not.

    (one thread he's found seems to indicate not:
    http://archives.postgresql.org/pgsql...4/msg00075.php)

    Hope someone can clear this up for us quickly

    Thanks

    Flawless
    Last edited by Flawless_koder; May 2, 2002 at 02:37.
    ---=| If you're going to buy a pet - get a Shetland Giraffe |=---

  2. #2
    SitePoint Enthusiast
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, let's hope this thread doesn't just turn into a flaming rant...

    I develop using SQL Server. My definition of a stored procedure is the SQL Server one.

    <snip from MS SQL2K BOL>
    Stored procedures in SQL Server are similar to procedures in other programming languages in that they can:

    Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.

    Contain programming statements that perform operations in the database, including calling other procedures.

    Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).
    You can use the Transact-SQL EXECUTE statement to run a stored procedure. Stored procedures are different from functions in that they do not return values in place of their names and they cannot be used directly in an expression.

    The benefits of using stored procedures in SQL Server rather than Transact-SQL programs stored locally on client computers are:

    They allow modular programming.
    You can create the procedure once, store it in the database, and call it any number of times in your program. Stored procedures can be created by a person who specializes in database programming, and they can be modified independently of the program source code.

    They allow faster execution.
    If the operation requires a large amount of Transact-SQL code or is performed repetitively, stored procedures can be faster than batches of Transact-SQL code. They are parsed and optimized when they are created, and an in-memory version of the procedure can be used after the procedure is executed the first time. Transact-SQL statements repeatedly sent from the client each time they run are compiled and optimized every time they are executed by SQL Server.

    They can reduce network traffic.
    An operation requiring hundreds of lines of Transact-SQL code can be performed through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.

    They can be used as a security mechanism.
    Users can be granted permission to execute a stored procedure even if they do not have permission to execute the procedure's statements directly.
    </snip>

    I think that about covers it. The way I see it, /unless/ postgres compiles the procedure, it's not a stored procedure; and even if it does, it still may not be one, based on features such as returning recordsets, allowing/denying access etc..

    Comments?

  3. #3
    Perl/Mason Guru Flawless_koder's Avatar
    Join Date
    Feb 2002
    Location
    Gatwick, UK
    Posts
    1,206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well as my first post stated - the procedures are converted to binary, and stored.

    To me this means they're "stored procedures".

    The term recordset is language specific (asp) isn't it?
    (i might be wrong...)
    ---=| If you're going to buy a pet - get a Shetland Giraffe |=---

  4. #4
    SitePoint Enthusiast
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: Postgres Functions - are they stored procedures

    Originally posted by Flawless_koder
    The produced bytecode is identified in the call handler by the object ID of the function.
    "Bytecode" indicates that the code is NOT compiled, as in Java. A platform independant, language specific version of the code is produced, which is taken by the machine interpreter (forgotten the correct term) at runtime and converted into binary instructions which are then executed.

  5. #5
    Perl/Mason Guru Flawless_koder's Avatar
    Join Date
    Feb 2002
    Location
    Gatwick, UK
    Posts
    1,206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Postgres SQL provides three types of functions:

    query language functions (functions written in SQL)

    procedural language functions (functions written in, for example, PLTCL or PLSQL)

    programming language functions (functions written in a compiled programming language such as C)
    ---=| If you're going to buy a pet - get a Shetland Giraffe |=---

  6. #6
    SitePoint Enthusiast
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, I think I could have explained myself better...

    From what you've said, Postgres functions may contain or call compiled code, such as C, and from what you've said off-thread, your C would be compiled by a regular C compiler such as gcc/bcc. My take in this is that "if the C stated 'select * from mydb.table', this would be compiled into the equivalent binary version of the statement.

    If the function containing that C was called (whether it was contained inside or outside of the database itself), the DBMS would still have to calculate the execution plan for the code, as the execution plan isn't known by the C compiler when the C code is compiled.

    SQL Server calculates this execution plan when the stored procedure is compiled. Thus, even though you're using compiled code, your DBMS still has to calculate an execution plan, and as so, your "stored procedure" is no more than "a set of stored queries".

    Ofcourse, I may be wrong

  7. #7
    Perl/Mason Guru Flawless_koder's Avatar
    Join Date
    Feb 2002
    Location
    Gatwick, UK
    Posts
    1,206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Though this doesn't refer to the C compiled functions, it does give an idea how the functions work:

    For all expressions and SQL statements used in the function, the PL/pgSQL bytecode interpreter creates a prepared execution plan using the SPI manager's SPI_prepare() and SPI_saveplan() functions. This is done the first time the individual statement is processed in the PL/pgSQL function. Thus, a function with conditional code that contains many statements for which execution plans would be required, will only prepare and save those plans that are really used during the lifetime of the database connection.

    To get around this restriction, you can construct dynamic queries using the PL/pgSQL EXECUTE statement --- at the price of constructing a new query plan on every execution.
    This means that an execution plan is only generated foreach connection. Although this is optimised, it's not as efficient as the SQL version using T-SQL, and would perhaps query my theory that Functions are stored proceedures.

    These Quotes refer to the Procedural function type. I am looking now for something to state when execution plans for Compiled programming functions are created. I'd think that they're created on dynamic loading first time round - which would really define only 1/3 of the Postgres functions types (compiled programming functions) as actual stored procedures.

    Anything to comment on this Mullie?

    Flawless
    ---=| If you're going to buy a pet - get a Shetland Giraffe |=---

  8. #8
    SitePoint Enthusiast
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you can find something which states something along the lines of "the execution plan is only calculated once within Postgres for functions comprised of purely compiled code", I will happily concede that postgres has stored procedures of a sort.

    There are still worries about user permissions, returning recordsets etc... but those are niceties, not necessities.

    I still stick by my previous post though... I don't /think/ there's any way the C would be compiled into an execution plan from outside the database. Going by your last post, it would be likely that the same would hold true for the durations of the execution plans - the length of database connection.

  9. #9
    Perl/Mason Guru Flawless_koder's Avatar
    Join Date
    Feb 2002
    Location
    Gatwick, UK
    Posts
    1,206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can you please define recordset for those of us
    whose soul won't be tortured in hell for eternity
    for swearing our alligance to ASP ?

    Thanks

    Flawless
    ---=| If you're going to buy a pet - get a Shetland Giraffe |=---

  10. #10
    SitePoint Enthusiast
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Talk about putting someone on the spot :P

    A recordset is, as the name implies... "a set of records". The records will be produced by some sort of SQL "SELECT" statement, for example "SELECT * FROM tblMyTable" produces a recordset containing all of the rows (tuples) from the table named "tblMyTable" in the current database.

    Recordsets don't only apply to ASP, they apply to SQL itself, and any other language I know of which interfaces in any way with databases (e.g PHP). They do however go by alternative names sometimes, such as "Rowsets", "Resultsets" etc...

    <edit>
    Two small notes:

    1) Not all SELECT statements produce a recordset, for example "SELECT TOP 1 @UserID = UserID FROM tblUsers ORDER BY UserID DESC" will put the last UserID created in tblUsers into the variable @UserID (assuming the UserID field is an autoincrementing field). This example is T-SQL... I'm sure that it would be possuble in Postgres too, but have no idea how.

    2) Depending on your language of choice, it may be possible to construct and populate a recordset without using data from a database, althought this is MUCH less common
    </edit>
    Last edited by mulletboy2; May 2, 2002 at 06:21.

  11. #11
    Perl/Mason Guru Flawless_koder's Avatar
    Join Date
    Feb 2002
    Location
    Gatwick, UK
    Posts
    1,206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    he's cute, isn't he Ladys and Gentlemen...

    ... and guess what - he can be yours for a mesely £100 an hour.

    Yes .. you heard me ... your very OWN sql guru with tainted morals/ethics and a tendancy towards microsft - for just £100.

    There's only one ... so let's hear some offers!

    </ market shouting >

    Thanks for the definition Mullie
    ---=| If you're going to buy a pet - get a Shetland Giraffe |=---

  12. #12
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Stored Procedures != Functions

    You might be able to say functions are a subset of stored procedures but you'd be wrong. Stored procedures are more than taking an input and returning an output.

    Functions are used on columns in a query, e.g. SELECT max( someintcol ), etc.. and return a value, not a set.

    Whereas stored procs are used by themselves:
    EXEC get_user_information $userid

    and typically return sets (along with output parameters).

    SPs are, as we know, pre parsed, compiled, interpreted, etc.. This means that when you say 'EXEC get_user_information 435' (EXEC is not required but keeps things clear in this example) it already has the optimal path to get user information for 435.

    It would probably behoove you to see what PostgreSQL does for each of the three function types, and examples of their usage.

  13. #13
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    P.S. result or record set is in no way a Microsoft invention.

  14. #14
    Perl/Mason Guru Flawless_koder's Avatar
    Join Date
    Feb 2002
    Location
    Gatwick, UK
    Posts
    1,206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Matt!

    However you don't have to call functions like that either:

    Perform update_job(bla,bla,bla);

    Given this information, and an example of a procedure/Function in postgres (the 2nd type, a procedural language function, not a programming language function) below, is your conclusion still the same?

    Thanks for your response Matt

    An example Procedure/Function from a postgres example:
    Code:
    CREATE FUNCTION update_job (int4,text,text,int4) RETURNS int2 AS '
    DECLARE
        c_id_ins int4; j_name_ins text;  l_ins text; 
        job_id1 ALIAS FOR $4; oid1 int4; test_id int4 := 0;
        record1 RECORD; record2 RECORD; record3 RECORD; record4 RECORD; record5 RECORD;  
        return_int4 int4 := 0; job_no1 int4 := 0;
    BEGIN
       j_name_ins := $2; l_ins  := $3; c_id_ins := $1;
    
         -- A few Perl procedures. 
         -- Clean the name of the job.
       SELECT INTO record4 clean_text(j_name_ins) as text1;
       j_name_ins = record4.text1;
         -- Clean the location of the job. 
       SELECT INTO record5 clean_text(l_ins) as text1;
       l_ins = record5.text1;
         -- Verify the values we insert are okay.
       SELECT INTO record3 job_values_verify (c_id_ins, j_name_ins, l_ins) as no;
       IF record3.no < 0 THEN return (record3.no); END IF;
    
         -- See if there is a duplicate job name for that contact.
       FOR record1 IN SELECT job_id FROM jobs  
          where contact_id = c_id_ins and job_name = j_name_ins
            and job_id != job_id1
          LOOP
          test_id := record1.job_id;
       END LOOP;
         -- If the job_id is null, great, otherwise abort and return -1;
       IF test_id > 0 THEN return (-1); END IF;
    
         -- See if the job exists, otherwise return -2.
       FOR record1 IN SELECT * FROM jobs where job_id = job_id1  
          LOOP
          update jobs set contact_id = c_id_ins,  
            job_name = j_name_ins, job_location = l_ins
    	where job_id = job_id1;
          GET DIAGNOSTICS return_int4 = ROW_COUNT;
          test_id := 1;
          job_no1 := record1.job_no;
       END LOOP;
    
         -- If the job does not exist, what are we updating? return error. 
       IF test_id = 0 THEN return (-2); END IF;
    
         -- Everything has passed, return return_int4.
       insert into jobs_backup (contact_id, job_no, job_name, job_location, action, error_code, job_id)
            values (c_id_ins, job_no1, j_name_ins, l_ins, ''update'', return_int4, job_id1);
       return (return_int4);
    END;
    ' LANGUAGE 'plpgsql';
    ---=| If you're going to buy a pet - get a Shetland Giraffe |=---

  15. #15
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by Flawless_koder
    Thanks Matt!

    However you don't have to call functions like that either:

    Perform update_job(bla,bla,bla);

    Given this information, and an example of a procedure/Function in postgres (the 2nd type, a procedural language function, not a programming language function) below, is your conclusion still the same?
    Well, PostgreSQL mis-naming doesn't change my conclusion that functions & SPs behave in a manner I suggested (at least in typical naming conventions in Syb/MS/Oracle).

    Once they start being able to return result sets and more than one output parameter (it appears that only one param can be returned) I'd be inclined to call them Stored Procedures. Plus the whole compilation/query plan thing (it sounds like they store the plan?).

  16. #16
    Perl/Mason Guru Flawless_koder's Avatar
    Join Date
    Feb 2002
    Location
    Gatwick, UK
    Posts
    1,206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The following example from a site would seem to indicate multiple examples can be returned:

    The following more interesting example takes a single argument of type EMP, and retrieves multiple results:

    CREATE FUNCTION hobbies (EMP) RETURNS SETOF hobbies
    AS 'SELECT hobbies.* FROM hobbies
    WHERE $1.name = hobbies.person'
    LANGUAGE 'sql';
    Though this example is using the first type of function, using this method with the third type (C/Programming functions [externally compiled]) would produce the right effect, i think.

    What do you think Matt / Mullie ?
    ---=| If you're going to buy a pet - get a Shetland Giraffe |=---

  17. #17
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So it appears that they are calling stored procedures functions. The other RDBMS' actually make the distinction and impose limitations on functions vs. stored procedures.

    e.g.
    Code:
    CREATE PROCEDURE dbo.bob
      @input_param1 INT,
      @input_param2 VARCHAR( 200 ) DEFAULT 'NONE',
      @success      BIT OUTPUT
    AS
      IF @input_param2 = 'NONE' 
        SELECT *
          FROM table
         WHERE id = @input_param1
      ELSE
        SELECT *
          FROM table
         WHERE id      = @input_param1
           AND somecol = @input_param2
    
      SET @success = 1
    Code:
    CREATE FUNCTION dbo.multiply
      @param1 INT,
      @param2 INT
    AS
      RETURN @param1 * param2
    They perform two distinct operations and have limits placed on them. They both use T-SQL, but functions are required not to return sets. Stored procedures do not have limitations. You can return a single set, or multiple result sets, or multiple output parameters (if you wanted it to operate like a C or PHP function with many variables passed byref) or any combination of the above.

    Again, it seems like PostgreSQL simply calls everything a function even though it behaves somewhat like a stored procedure (can they return more than one set? Or a combination of output parameters?).

    Really the nomenclature isn't terribly important – if it performs how you want it to, then you’re good to go.

  18. #18
    Perl/Mason Guru Flawless_koder's Avatar
    Join Date
    Feb 2002
    Location
    Gatwick, UK
    Posts
    1,206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Agreed Matt.
    I don't use the sql version myself-
    i use the Perl/PgSQL or C version of functions/procedures.

    However Mullie and i ARE just big kids - so these kinds of discussions give us a chance to throw our toys at each other from opposing prams

    Flawless
    ---=| If you're going to buy a pet - get a Shetland Giraffe |=---

  19. #19
    SitePoint Enthusiast
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My pram's nicer than yours, and my mummy doesn't dress me in pink

  20. #20
    SitePoint Addict EvilDoppler's Avatar
    Join Date
    Dec 2001
    Location
    Perth, Australia
    Posts
    217
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking

    I vote for Flaw....at least pink is HIS colour!!!

  21. #21
    Perl/Mason Guru Flawless_koder's Avatar
    Join Date
    Feb 2002
    Location
    Gatwick, UK
    Posts
    1,206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    he he - doesn't it just

    Flawless
    ---=| If you're going to buy a pet - get a Shetland Giraffe |=---


Bookmarks

Posting Permissions

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