Re: [SQL] Email function using c instead of tclu
>
> Hi,
> I have tried without any success to get this pgmail for tclu resolved.
> Does anyone have or know of the same sort of function as pgmail but
> supporting c, as this is one of the installed languages I have access
> to under postgresql. So for instance:
>
> CREATE FUNCTION sendemail(x,x)
>
> LANGUAGE 'c';
>
> regards
>
>
> Uzo
>
>
Pretty late and pretty old stuff of mine (Sep 19 2001)
but it should still work or at least give you some hint.
Regards, Christoph
I'm working on a HP-UX system, so some of the
following has to be adapted, but in principle
it's the same on every system and it works.
First piece of code is a standalone program,
which you should always write and test before
you start creating C functions inside PostgreSQL.
Second piece is your sendemail function slightly
modified to make it run on my system.
When I do a
select sendemail('ch', 'was soll das?') ;
I'm receiving an email from postgres.
Regards, Christoph
First piece:
/*
cc -Aa -g -I/opt/pgsql/include/ -c sendemtest.c
cc sendemail.o sendemtest.o -o sendemtest
*/
#include
#include
#include
#include "postgres.h"
void main() {
char buf[1024];
int ln;
text *res;
text *to;
int sendemail(text *email,text *message);
strcpy(buf, "Kissenminister Aussinger \n");
ln = strlen(buf);
res = (text *) malloc(VARHDRSZ + ln);
memset(res, 0, VARHDRSZ + ln);
res->vl_len = VARHDRSZ + ln;
memcpy(res->vl_dat, buf, (int) ln);
strcpy(buf, "ch");
ln = strlen(buf);
to = (text *) malloc(VARHDRSZ + ln);
memset(to, 0, VARHDRSZ + ln);
to->vl_len = VARHDRSZ + ln;
memcpy(to->vl_dat, buf, (int) ln);
sendemail(to, res);
}
Second piece:
/*
cc -Aa -g -I/opt/pgsql/include/ +z -c sendemail.c
ld -b -o sendemail.sl sendemail.o
CREATE FUNCTION sendemail(text,text) RETURNS int4
AS '/fdsk2/users/ch/tools/pgsql.mydoc/sendemail.sl' LANGUAGE 'c';
DROP FUNCTION sendemail(text,text);
*/
#include
#include
#include "postgres.h"
int sendemail(text *email,text *message)
{
int result = 0 ;
char string_tosend [300];
sprintf(string_tosend,"/usr/bin/echo \"%s\"
>/tmp/mailtmp.txt\n",VARDATA(message));
result += system(string_tosend);
sprintf(string_tosend,"/usr/bin/mail -dt %s
Re: [SQL] problem with function trigger
> > Hi I'm trying to update a table column with a pl/pgsql function and a > trigger. > But I didn't managed to make it work so far. > > Here's my function code : > > CREATE FUNCTION public.calcul_impact() RETURNS opaque AS ' > > DECLARE > id_line integer; > quantity integer; > single_price real; > total_cost real; > amort integer; > month integer; > impact real; > > BEGIN > > SELECT INTO id_line id_line_table FROM table WHERE id_line_table = > NEW.id_line; > SELECT INTO single_price single_price_previ FROM table WHERE id_line_table > = NEW.id_line; > SELECT INTO total_cost total_cost_previ FROM table WHERE id_line_table = > NEW.id_line; > SELECT INTO quantity quantity_previ FROM table WHERE id_line_table = > NEW.id_line; > SELECT INTO amort amortis FROM table WHERE id_line_table = NEW.id_line; > SELECT INTO month month_previ FROM table WHERE id_line_table = > NEW.id_line; > SELECT INTO impact impact_previ FROM table WHERE id_line_table = > NEW.id_line; > > IF(quantity IS NULL OR single_price IS NULL) THEN impact:= 0; > ELSE IF(quantity >= 12) THEN impact:= (total_cost / amort); > ELSE IF(quantity < 12 AND single_price <= 500) THEN impact:= > total_cost; > ELSE IF(quantity < 12 AND single_price > 500) THEN impact:= ((12 > - month)*(total_cost/(amort*12))); >END IF; > END IF; > END IF; > END IF; > > IF (TG_OP =''INSERT'' OR TG_OP=''UPDATE'') THEN > UPDATE table SET impact_previ = impact WHERE id_line_table = > NEW.id_line; > END IF; > > RETURN NEW; > > END; > > ' LANGUAGE 'plpgsql'; > > CREATE TRIGGER add_impact_previ BEFORE INSERT OR UPDATE ON public.budget > FOR EACH ROW EXECUTE PROCEDURE calcul_impact(); > > I always get the error : > > Error SQL : > ERROR: record "new" has no field named "id_ligne" > > Has anyone an idea about what's wrong ? > > thanks for answering me Could you please show the definition of table "budget". Not sure what you're trying to achieve by these SELECT INTO ... statements. Regards, Christoph ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] sql query with join and parameter in postgresql function
Hello, I'd like to write a function similar to that: CREATE FUNCTION public.report_join_parameter(char(6)) RETURNS ??? AS ' SELECT tableA.field1, tableA.field2, tableB.field1, tableB.field2, tableC.field1, tableC.field2, FROM tableA INNER JOIN tableB ON tableA.pk = tableB.fk INNER JOIN tableC ON tableB.pk = tableC.fk WHERE tableC.field3= 0 AND tableB.field1= $1 ' LANGUAGE 'sql' VOLATILE; Do I ***HAVE TO*** define a composite type for the columns my function is supposed to return? Isn't there a simpler method, that could allow me to avoid this extra work? I thought a view would be more appropriate, but if I'm not wrong, a view does not accept any parameter as input... Thanks! Philippe ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] sql query with join and parameter in postgresql function
On Jan 16, 2004, at 8:30 PM, Philippe Lang wrote: Hello, I'd like to write a function similar to that: CREATE FUNCTION public.report_join_parameter(char(6)) RETURNS ??? AS Do I ***HAVE TO*** define a composite type for the columns my function is supposed to return? As far as I know. It's not that hard. CREATE TYPE report_join_type ( tableAfield1 , tableAfield2 , tableBfield1 , tableBfield2 , tableCfield1 , tableCfield2 ); I thought a view would be more appropriate, but if I'm not wrong, a view does not accept any parameter as input... But you can just define the view and select it with a WHERE clause with your parameter, just like a normal table. Would that help? Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] sql query with join and parameter in postgresql function
> > I thought a view would be more appropriate, but > > if I'm not wrong, a view does not accept any parameter > > as input... > > But you can just define the view and select it with a WHERE clause with > your parameter, just like a normal table. Would that help? Thanks! That's perfect. I join in a view, and limit in a function, with a WHERE clause. And no need for any redundant composite type, although I agree this is not that complicated to use... Philippe Lang ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
