Thread: non-cachable 'C' language functions

(using postgresql 6.5.2)

I have created a set of postgres extension functions in C (which use SPI
to perform queries), and added them to my database with something like
this:

CREATE FUNCTION my_next_uid(text) RETURNS text AS  '/usr/lib/pgsql/my_uids.so' LANGUAGE 'c';

My functions are designed to behave like nextval() and friends, except
that they operate on a varchar field in a predetermined table, rather than
a database sequence.  For example, my_next_uid() should always return a
unique value, incrementing the current value in said table each time it is
called.

So far, my functions appear to work correctly.  However, looking at their
entries in the pg_proc table, I see that their "proiscachable" fields are
set to true.  This worries me, because my understanding is that postgres
will re-use values returned by cachable functions, which is undesirable.
(In order for my_next_uid() to be useful, it must retrieve a new value
each time it is used.)

Is my understanding correct?  What should I do about it?  The postgresql
6.5.2 docs for CREATE FUNCTION don't tell me how to make my functions
non-cachable.

Regards,

Forest Wilkinson

I'm probably staring right at it. (One of the difficulties with RTFMing, is
having too many docs!)

Is there anything in the API that produces the week of the year, from 1 to
52 or 53 depending on the week of the year, and the days that are in that
week?

Many thanks.
                                         -dlj.

Forest Wilkinson <fspam@home.com> writes:
> Is my understanding correct?  What should I do about it?  The postgresql
> 6.5.2 docs for CREATE FUNCTION don't tell me how to make my functions
> non-cachable.

6.5 doesn't pay any attention to proiscachable, AFAIR.  7.0 does, but
it defaults to assuming proiscachable = FALSE; you have to say
"with (iscachable)" in CREATE FUNCTION to get the other behavior.

There is a problem in both versions that WHERE clauses containing
no variables (table fields) will be assumed to be constants even
if they contain non-cachable function calls :-(.  Thus, for example,select * from foo where random() < 0.5
doesn't work as desired.  I plan to fix this for 7.1.

Offhand I don't see a use for a nextval-like function in WHERE,
so you're probably safe with both 6.5 and 7.0.
        regards, tom lane

Got it: -U with date.
                          -dlj.


----- Original Message -----
From: "David Lloyd-Jones" <david.lloyd-jones@attcanada.ca>
To: <pgsql-sql@postgresql.org>
Sent: Friday, August 11, 2000 9:27 PM
Subject: [SQL] Week of the Year?


> I'm probably staring right at it. (One of the difficulties with RTFMing,
is
> having too many docs!)
>
> Is there anything in the API that produces the week of the year, from 1 to
> 52 or 53 depending on the week of the year, and the days that are in that
> week?
>
> Many thanks.
>
>                                           -dlj.
>
>
>
>

Try using the function date_part such as:

select date_part('week',now());

"and the days that are in that week" I guess want to answer a question
such as:
Given a date, what is first date in that same week, and what is the last
date in that week. There are a couple of approaches to this. My first was:

select
to_date(date_part('year',now()),'YYYY')+(7*date_part('week',now()));

and the above +6 to the the last day of the week. Another approach for
this same question is much simplier (if the question is indeed what you
are asking)

select now()-date_part('dow',now());

This last select gives the Sunday for the current week. To get the
Saturday, simply:

select now()-date_part('dow',now())+6;

Of course, replace the now() with whatever contains the date or timestamp.

John McKown

> I'm probably staring right at it. (One of the difficulties with RTFMing, is
> having too many docs!)
> 
> Is there anything in the API that produces the week of the year, from 1 to
> 52 or 53 depending on the week of the year, and the days that are in that
> week?
> 
> Many thanks.
> 
>                                           -dlj.
> 
> 
>