Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002

    Unanswered: Is Function = Stored Procedure in PostgreSQL?

    I am new to PostgreSQL.

    I would like is there anything in PostgreSQL like stored procedure in MSSQL?

    I guess Function in PostgreSQL is, right?
    I would like to ask how to execute the Function defined?


  2. #2
    Join Date
    Mar 2004
    I do not know that much about MSSQL but i think postgres functions are like stored procedures in MSSQL.

    In postgresql there are several languages u can use to write your function in. One of them is pl/pgsql.

    Once have created a function, you can excute it by:

    select function_name().

    If you create a function like:

    CREATE FUNCTION function_name(integer,integer) RETURNS TEXT AS'

    You can only fire up that function by calling the function with 2 integer values: (a null value is also an option but you gotta type null then)
    select function_name(1,2)

    Hope this helps a bit


  3. #3
    Join Date
    Nov 2002

    Is Function = Stored Procedure in PostgreSQL?

    Thanks for you reply first.

    I have written a function to accept an integer, a string and an datetime as parameters.
    i.e. function_A(int8, bpchar, timestamp)

    when I tried to run in query tool, I type
    select "DataBaseName"."function_A"(1, 'a', '2004-04-04 10:00:00'), it gives error.

    I would like to know how to type a timestamp and a string in PostgreSQL.
    For string, it is just quoted by single quote/double quote?
    And what is the format to type a timestamp?
    '2004-04-04 10:00:00' seems not work.....


  4. #4
    Join Date
    Mar 2004

    im not a function wizard

    but first you have to decide what language you want to create the function in.
    Personally i find pl/pgsql the easiest to learn. (perhaps you can do more in c, but i dont know c).

    For you example something like this would go:

    CREATE FUNCTION my_function_name(integer,text,date) RETURNS TEXT AS'
    my_integer integer;
    my_text text;
    my_date timestamp;


    my_integer := $1;
    my_text := $2;
    my_date := $3


    if you work with dates in a function for example 01-01-2004: you have to escape the ' with a \ and you have to "cast it as a date" Example:

    if my_date >= CAST(\'01-01-2004\' AS DATE) THEN

    Im not sure if \'01-01-2004\'::date works, you should give it a try.
    Im not sure if you can cast it as a timestamp either tho but that is stuff you gotta try for yourself (i myself have just begun scratching the surface of functions and pl/pgsql)

    As for calling a function from the command / pgadmin III:

    SELECT my_function_name(2,' my text','01-01-2004')
    Again, you might need to escape the ' with \.

    Hope this helps a bit.


Posting Permissions

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