On Thu, 27 Nov 2003, Joe Conway wrote:
> Andreas Tille wrote:
> > test=# select PLpgSQLDepartmentSalaries() ;
>
> This should be:
> regression=# select * from PLpgSQLDepartmentSalaries();
> departmentid | totalsalary
> --+-
> 1 |
On Wed, 26 Nov 2003, Richard Huxton wrote:
> Not as you've done it. You could pass in text "(1,2,3)", build your query and
> use EXECUTE to execute it.
This boils down the question to the problem which occured with
your promissing link below, because I need to use PL/pgSQL, right?
> Alternatively
Hi,
I want to write a function of the following type
CREATE FUNCTION test ( )
RETURNS setof MyTable
AS
'SELECT * FROM MyTable WHERE id IN $1'
LANGUAGE 'SQL' ;
I was not able to find a reference whether this is possible and if yes
how to specify the argumen
On Tue, 21 Jan 2003, Bruno Wolff III wrote:
> What response did you get to your message?
No response at all.
> I usually use [EMAIL PROTECTED] as the address to send mailing
> list commands to, though it is possible the address you tried works
> as well.
I normally send those command to majordomo
Hi,
once I subscribed to this list I've got the information how to
subscribe:
If you ever want to remove yourself from this mailing list,
send the following command in email to
[EMAIL PROTECTED]:
approve unsubscribe Andreas Tille <[EMAIL PROTECTED]>
Well, I did so bu
On 26 Aug 2002, Oliver Elphick wrote:
> Create a temporary table (no constraints)
>
> CREATE TEMP TABLE temptable AS
> (SELECT * FROM tablename LIMIT 1);
> DELETE FROM temptable;
>
> Copy all data into the temporary table
>
> COPY temptable FROM 'filepath
On Sun, 25 Aug 2002, Mark Stosberg wrote:
> Thanks for the clarification. Here's an idea about how to solve your
> problem. As you are importing your data, instead of doing it all at
> once, try import it a row at a time into a table that has the RI turned
> on. Check each insert to see if it's s
On Sat, 24 Aug 2002, Mark Stosberg wrote:
> On Thu, 22 Aug 2002, Andreas Tille wrote:
> > Hello,
> >
> > I want to solve the following problem:
> >
> > CREATE TABLE Ref( Id int ) ;
> > CREATE TABLE Import ( Idint,
>
Hello,
I want to solve the following problem:
CREATE TABLE Ref( Id int ) ;
CREATE TABLE Import ( Idint,
Other varchar(42),
Flag int,
Tstimestamp ) ;
CREATE TABLE Data ( Idint,
Other varchar
On Wed, 14 Aug 2002, Tom Lane wrote:
> I concur with Richard that Andreas needs to solve a different problem,
> but just for the record, the way you could do it is
>
> CREATE TABLE without mentioning the default
>
> CREATE FUNCTION
>
> ALTER TABLE ... SET DEFAULT
>
> Note howeve
On Wed, 14 Aug 2002 Richard Huxton wrote:
> > CREATE TABLE Items (
> > Idint DEFAULT NextItem()
>
> > CREATE FUNCTION NextItem() RETURNS INT4
> > AS 'select max(Id)+1 from Items where Id < 100;'
> > LANGUAGE 'sql';
>
> >ERROR: Relation "items" does not exist
> >
> > Any hin
Hello,
I want to insert new data into a table with increasing data set ids.
The table has two separate "regions" of data: Those with Ids below
100 and other. If I want to create a new Id in the "lower region"
I tried the following (simplified example):
CREATE TABLE Items (
Idint DEFA
Hello,
I tried to track down the database definitions from a more complex
database which I have to convert from MS SQL to PostgreSQL. I have
only one last syntactical error. Here is the striped down code
to the shortest snipped which shows the problem:
CREATE TABLE ResKulturDetail
(
IdLabN
On Thu, 9 Nov 2000, Josh Berkus wrote:
> 2. Stored Procedure functionality, i.e. outputting a full recordset from
> a function (or new structure, if functions are hard to adapt) based on
> the last SELECT statement passed to the function. An alternative would
> be to develop parameterized views,
On Mon, 6 Nov 2000, Grant Finnemore wrote:
> Whilst this is an unfortunate position at the moment, it has been my experience that
>it does not cause insurmountable problems.
> (Some short term headaches - yes. ;-)
After learning this as a fact you have short term headaches but before
you have co
On Mon, 4 Sep 2000, Tom Lane wrote:
> This is a bug that has already been fixed in current sources: the check
> that makes sure your select produces only one varchar column is
> mistakenly counting the hidden IdSort column that's needed to sort by.
Is there any patch against 7.0.2 sources which m
Hello
I tried to create the following function
CREATE Function pGetMenu ( int )
returns setof varchar
As '
SELECT IdMenuShow || IdWebPage
FROM Menu
WHERE IdMenu = $1
ORDER BY IdSort ;
' language 'SQL' ;
I've got the following error message:
ERROR: function declared t
On Thu, 31 Aug 2000, Jan Wieck wrote:
First of all thank you very much for your effort!
This is definitely a good argument for PostgeSQL to have such fast
support in adding features which might convince some of my collegues
or my boss :).
> Could you try out the current CVS sources?
Well, I
On Mon, 28 Aug 2000, Yury Don wrote:
> > Create Function VarSelect ( varchar, varchar )
> >returns int
> >As '
> > Declare num int ;
> >
> > Begin
> >Select Into num Count(*) From $1 Where $2 ;
> >return num;
> > End ;
> >' language 'plpgsql' ;
> >
> >
On Mon, 28 Aug 2000, Yury Don wrote:
> Andreas Tille wrote:
> >
> > Create Function VarSelect ( varchar, varchar )
> >returns int
> >As '
> > Declare num int ;
> >
> > Begin
> >Select Into num Count(*
Hello,
I want to use the following construct in a stored function:
Create Function VarSelect ( varchar, varchar )
returns int
As '
Declare num int ;
Begin
Select Into num Count(*) From $1 Where $2 ;
return num;
End ;
' language 'plpgsql' ;
Could someo
On Fri, 25 Aug 2000, hlefebvre wrote:
> Tom Lane wrote:
> > NEW.ChangedAt := now();
> > to prevent the system from reducing timestamp('now') to a constant
> > when the function is first executed.
> >
> > regards, tom lane
> yep you're right :
You both are comple
On Fri, 25 Aug 2000, Tom Lane wrote:
> I think you are getting burnt by premature constant folding --- see
> nearby discussion of how to define a column default that gives the
> time of insertion. You need to write this as
> NEW.ChangedAt := now();
> to prevent the system from reducing tim
On Fri, 25 Aug 2000, hlefebvre wrote:
> No I suppose that the problem is the identifier "changedat" is unknown.
>
> You must probably prefix it : NEW.changedat
>
> CREATE FUNCTION changed_at_timestamp() RETURNS OPAQUE AS '
> BEGIN
> NEW.ChangedAt := timestamp(''now'');
>
On Wed, 23 Aug 2000, hlefebvre wrote:
> Yes. The keywords NEW / OLD are available only in triggers
> see
> http://www.postgresql.org/users-lounge/docs/7.0/user/c40874113.htm#AEN4286
Well, I believe that, but
CREATE FUNCTION changed_at_timestamp() RETURNS OPAQUE AS '
BEGIN
ChangedAt
On Thu, 24 Aug 2000, Hiroshi Inoue wrote:
> Hmm,Andreas's original function seems to contain other statements.
> If the function contains DML statements for the table Temp_Num_Table,
> it wouldn't work properly. i.e 1st call would work but 2nd call woudn't.
That's the problem. I'm in the process
Hello,
I striped down my function up to a strange thing:
web=# create function pHelpTable( )
web-# returns int
web-# As '
web'# Begin
web'# Create Table Temp_NumTable ( Num int ) ;
web'#
web'# return 17 ;
web'# End; '
web-# language 'plpgsql' ;
CREATE
web=#
web=# s
On Wed, 23 Aug 2000, hlefebvre wrote:
> create table mytable( CreateDate timestamp default timestamp('now'),
> );
I've done a pg_dump and there this line was transformed to:
"createdat" timestamp DEFAULT '23.08.2000 15:35:16.00 CEST'::"timestamp",
I'm afraid if I ever should use this
On Wed, 23 Aug 2000, hlefebvre wrote:
> create table mytable( CreateDate timestamp default timestamp('now'),
> );
Thanks, this works.
> CREATE FUNCTION myt_stamp () RETURNS OPAQUE AS
> BEGIN
> ChangeDate := timestamp(''now'');
> RETURN NEW;
> END;
> ' LANGUAGE 'pl
On Wed, 23 Aug 2000, Jan Wieck wrote:
> You can't. Not with PL/pgSQL nor with any other PL or C. The
> problem is nested deeper and requires the planned querytree
> redesign to get solved.
>
> Before you ask: The mentioned redesign will NOT be done for
> 7.1, and I'm not
Hello,
could someone enlighten a fairly beginner how to define columns
of a table with the following features:
CreateDate DEFAULT value should store current date and time
ChangeDate DEFAULT value at creation as above and a Trigger
function which stores data end time of any ch
On Mon, 21 Aug 2000, Stephan Szabo wrote:
> I haven't thought of an elegant way to do it, although you could
> fake some of it with a table of the appropriate structure with a sequence.
> It's really ugly, but the pl/sql(tcl/perl/etc...) function gets the
> next value of the sequence and inserts
On Thu, 17 Aug 2000, Stephan Szabo wrote:
> What you may need to do is declare a variable of type record
> and do SELECT INTO * From ... rather than just
> the SELECT.
Thanks, that worked.
> Yeah, setof seems fairly wierd. SETOF basetype if
> you do a SELECT FROM table seems to work though
On Wed, 16 Aug 2000, Stephan Szabo wrote on [EMAIL PROTECTED]:
(sorry for the crossposting, just to tell the list that I now switched to
the right one hopefully)
> I think the thing is that most people don't have basic examples, they
Perhaps someone knows one nice doc. I only found some hints fo
34 matches
Mail list logo
