Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    8

    Unanswered: The complex case needed your help

    Dear all,
    I am a newbie of postgresql. I am try to do myself the best but I am still stuck.
    here is my question:
    I need a function in postgresql with plpgsql (or something else) to do:

    create or replace function foo(text,text) return integer as

    declare
    my_num1 integer;
    my_num2 integer;
    result integer;

    begin

    mynum_1 = "select my_field from my_table1 where field_1=" $1 " limit 1"

    mynum_2 = "select my_field from my_table2 where field_1=" $2 " limit 1"
    result=my_num1+my_num2;
    return result;
    end




    So, my stuck is I really don't know how to execute the query and return it to my variables(mynum_1,2...)

    Please help me

  2. #2
    Join Date
    Mar 2004
    Posts
    110
    Well, im a noobie too. Didnt no jack sh!t about databases 2 months ago but ill give it a shot.

    At first glance (but you prolly forgot to copy/paste it in):
    you have no ' LANGUAGE 'plpgsql' in the function.

    Further more you state:
    mynum_1 = "select my_field from my_table1 where field_1=" $1 " limit 1"

    i think you forgot to put in a ":="
    it should be:

    mynum_1 := "select my_field from my_table1 where field_1=" $1 " limit 1"

    Not sure if that syntax would work, personally i use something diffrent.
    If i had to write your function it would look like this:

    create or replace function foo(text,text) return integer as '

    declare

    rec record;
    input_table1 text;
    input_table2 text;
    my_num1 integer;
    my_num2 integer;
    result integer;

    begin

    input_table1 := $1;
    input_table2 := $2;

    SELECT INTO rec my_field FROM my_table1 WHERE field_1 = input_table1 LIMIT 1;
    my_num1 := rec.my_field;

    SELECT INTO rec my_field FROM my_table2 WHERE field_1 = input_table2 LIMIT 1;
    my_num2 := rec.my_field;

    result := my_num1 + my_num2;

    return result;

    end;

    ' LANGUAGE ' plpgsql';

    Hope it works, havent tested it.

    -Ed

    *edit* there may be some typos in the function... already got one out.. be careful

  3. #3
    Join Date
    Apr 2004
    Posts
    8

    live is live

    My god,

    While I am posting this sh!t topic in here, I am trying to trying do my function, so, It's work.
    After all, I am planing to go here and post my new discover and i saw your post.

    Yeah, as you said, the problem is the record type...

    Anyway, thank for your help....

    ah, I have another question

    How to send mail from a function of posgresql
    like this

    reate Procedure sp_SMTPMail

    @SenderName varchar(100),
    @SenderAddress varchar(100),
    @RecipientName varchar(100),
    @RecipientAddress varchar(100),
    @Subject varchar(200),
    @Body varchar(8000),
    @MailServer varchar(100) = '34.224.26.131'

    AS

    SET nocount on

    declare @oMail int --Object reference
    declare @resultcode int

    EXEC @resultcode = sp_OACreate 'SMTPsvg.Mailer', @oMail OUT

    if @resultcode = 0
    BEGIN

    EXEC @resultcode = sp_OASetProperty @oMail, 'RemoteHost', @mailserver
    EXEC @resultcode = sp_OASetProperty @oMail, 'FromName', @SenderName
    EXEC @resultcode = sp_OASetProperty @oMail, 'FromAddress', @SenderAddress

    EXEC @resultcode = sp_OAMethod @oMail, 'AddRecipient', NULL, @RecipientName, @RecipientAddress

    EXEC @resultcode = sp_OASetProperty @oMail, 'Subject', @Subject
    EXEC @resultcode = sp_OASetProperty @oMail, 'BodyText', @Body


    EXEC @resultcode = sp_OAMethod @oMail, 'SendMail', NULL

    EXEC sp_OADestroy @oMail
    END


    SET nocount off
    Last edited by phonghtn; 04-21-04 at 14:53.

  4. #4
    Join Date
    Mar 2004
    Posts
    110
    I hope someday to do db stuff for a living but for now im constantly encountering the worst possible errors one could have, every time i try to do stuff a bit diffrent that the example

    -Ed

  5. #5
    Join Date
    Apr 2004
    Posts
    8

    How to retrieving data from stored procedure

    Hello again,
    I am using kylix 3 to developing my db application with postgresql server.

    I have already create function->result a dataset
    So, I can't use TStoreProcedure from Kylix to fetch data to data source, any idea

  6. #6
    Join Date
    Mar 2004
    Posts
    110
    Im sorry, but i have no idea what kylix 3 is or how it works. I just know just enough to fetch data with php.

    -Ed

Posting Permissions

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