Postgres Functions - are they stored procedures

[Flawless_koder]

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

[mulletboy2]

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?

[Flawless_koder]

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...)

[mulletboy2]

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.

[Flawless_koder]

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)

[mulletboy2]

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

[Flawless_koder]

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

[mulletboy2]

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.

[Flawless_koder]

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

[mulletboy2]

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>

[Flawless_koder]

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

[MattR]

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.

[MattR]

P.S. result or record set is in no way a Microsoft invention.

[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?

Thanks for your response Matt

An example Procedure/Function from a postgres example:

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';

[MattR]

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?).

[Flawless_koder]

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 ?

[MattR]

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.

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

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.

[Flawless_koder]

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

[mulletboy2]

My pram's nicer than yours, and my mummy doesn't dress me in pink

[EvilDoppler]

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

[Flawless_koder]

he he - doesn't it just

Flawless