Re: [SQL] variables in SQL??
> what im trying to do is have a Sum of a colum.. as it goes forwards with the > cursor.. > like so: > > Price|Sum > 5|5 > 4|9 > 10|19 > 2|21 > 7|28 I think what you mean is called running sum, I had the same problem before, and I found no other solution than creating a column for it, and calculating its values by a function. Yours, V.Paul
[SQL] Create table doesn't work in plpgsql
Hi,
can I do some table manipulation in plpgsql?
Look at only the "create table" line and the error message:
create function plural (text) returns text as '
begin
create table tmp (num int4);
return $1 || ''s'';
end;' language 'plpgsql';
select plural('test');
CREATE
ERROR: copyObject: don't know how to copy 611
What does the error message mean? Where can I read more about it?
Cheers, Volker
Re: [SQL] Create table doesn't work in plpgsql
Hi, > I don't think you can use DDL(data definition language) in PL/SQL. > create table is not DML(data munipulation language) instead > it's a DDL. Thanks, but that leaves me with a problem. What I really want to do is something like select str from person where id=1234; where str is a string that contains an expression like famname || ', ' || givname i.e. the final select is select famname || ', ' || givname from person where id=1234; I know it's possible by building the select e.g. in bash and calling psql with it as an argument, but do you see a possibility that is closer to Postgres, e.g. in plpgsql? Volker Paul
Re: [SQL] Invoice number
Hi, > I'm wondering how people creates guaranteed sequential numbers - in my case > for invoice numbers. See the PostgreSQL book p. 85 and 250, and the online doc about serials: CREATE TABLE person ( id SERIAL, name TEXT ); Volker Paul
Re: [SQL] Create table doesn't work in plpgsql
> Can this be done using tcl or perl? I'll try them and report what I find out. V.Paul
Re: [SQL] select returns no line
A space or something like that is also what I was thinking of. I'd suggest to: select * from users, length(user_login) where user_id=4; before and after the update. V.Paul
Re: [SQL] postgres's users take on onlamp
Well, maybe if Postgres' name was Mostgres, the "M"
would stand for Mostgres instead of MySQL ...
V.Paul
clayton cottingham wrote:
>
> heya:
> just wondering if anyone has any comments on this
>
> onlamp is o'rielly's new ideal
> that ,really, has been in use for quite a while
>
> its anacronym stands for
> linux apache mysql and {php/perl/python}
>
> more info here:
>
> http://www.onlamp.com/
[SQL] C/C++ interface
Hello,
in the C interface documentation there is an example using:
res = PQexec(conn, "DECLARE mycursor CURSOR FOR select * from
pg_database");
if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "DECLARE CURSOR command failed\n");
PQclear(res);
exit_nicely(conn);
}
PQclear(res);
res = PQexec(conn, "FETCH ALL in mycursor");
...etc. So the statements are:
DECLARE mycursor CURSOR FOR select * from pg_database;
FETCH ALL in mycursor;
What's the difference between this and simply doing:
select * from pg_database;
I tried this in psql, the result seemed the same.
What I'm really using, however, is the C++ interface.
Its documentation is not yet complete.
There, too, I tried a version with and without cursor.
The result seems to be the same, but returned int is always 0
for the version without cursor, so I get no information whether
the query succeeded.
Is someone maintaining the C++ interface and its documentation?
Thanks,
Volker Paul
Re: [SQL] C/C++ interface
Tom Lane wrote: > > Volker Paul <[EMAIL PROTECTED]> writes: > > Is someone maintaining the C++ interface and its documentation? > > Not really. Feel free to step up and lend a hand ... I found some functions of the C++ binding library that are not or scarcely documented, namely: int GetIsNull(int tup_num, int field_num); int GetIsNull(int tup_num, const char* field_name); int GetLine(char* string, int length); void PutLine(const char* string); const char* OidStatus(); int EndCopy(); I would like to complete the documentation at these points, and maybe add some small example C++ programs. In which form should I write the documentation and where should I send it? (I have no possibility at the moment to test SGML documents, i.e. convert them to HTML.) Regards, Volker Paul
RE: [SQL] psql problem
Does anyone know why when I am in a particular DB as user postgres and use the following statement, why I get this error?" This is the statement; SELECT * FROM some_file where ID = 1; [Volker Paul] If your ID field is really uppercase, try: SELECT * FROM some_file where "ID" = 1; Cheers, Volker
[SQL] Change type of column
Hello, how can I change the type of a column, e.g. from varchar() to text? Or is it possible to drop (i.e. delete) a column without creating a new table from the old one but without that column, dropping the old table and renaming the old to the new? Thanks, Volker Paul
Re: [SQL] Supported Encoding
> Does Postgresql support only EUC? Basically, I am trying to save > international > fonts to Postgresql, so I'm trying to find out what exactly I need to do. I'd suggest you try the Unicode UTF-8 encoding. It supports many languages, including Japanese of course, and needs no modification of existing programs, if you don't rely on character counting for output formatting. Volker Paul
Re: [SQL] problem with view and case - please help
> CREATE VIEW browser > AS > SELECT > agent_i, > CASE > WHEN agent_i LIKE '%MSIE 2.0;%' THEN 'Internet Explorer 2.0' > ... > agent_i NOT LIKE '%compatible%' THEN 'Netscape' > WHEN agent_i LIKE 'Mozilla/5.0 %' AND agent_i NOT LIKE '%MSIE%' AND > agent_i NOT LIKE '%compatible%' THEN 'Netscape' > WHEN agent_i LIKE 'Mozilla/6.0 %' AND agent_i NOT LIKE '%MSIE%' AND > agent_i NOT LIKE '%compatible%' THEN 'Netscape' > ELSE agent_i END AS navigateur, count (agent_i) > as total from access group by agent_i; > pqReadData() -- backend closed the channel unexpectedly. > This probably means the backend terminated abnormally > before or while processing the request. > We have lost the connection to the backend, so further processing is > impossible. Terminating. Query limit exceeded? At least that CASE statement looks rather clumsy, suggest using a function instead. V.Paul
Re: [SQL] Change attribute of a column
Hello, I asked a similar question before and it seems there is no way but using pg_dump. Question to the developers: Is it planned to make type changing (and casting) possible with ALTER TABLE or the like? Volker Bernie Huang wrote: > > Hi, > > I have a field in text[] and I want to change it to a datetime[]. Is it > possible? how? Thanks very much. > > - Bernie > > > > Bernie Huang <[EMAIL PROTECTED]> > Programmer > Environment Canada > Standards and Technology Services > > Bernie Huang > Programmer <[EMAIL PROTECTED]> > Environment Canada HTML Mail > Standards and Technology Services > 700-1200 West 73 Ave. Fax: (604)664-9195 > Vancouver Work: (604)664-9172 > BC Netscape Conference Address > V6P 6H9Netscape Conference DLS Server > Canada > Additional Information: > Last Name Huang > First NameBernie > Version 2.1
Re: [SQL] SQL (table transposition)
[EMAIL PROTECTED] wrote: > > Is there any way to get table T3 (below) from T1 and T2 using SQL (select, view, >etc)? > > T3 is basically all INDEX values from T1 matched to IND from T2 with the >corresponding KEY/VALUE pairs transposed from rows to columns. > > --- > |INDEX| (T1) > --- > | 1 | > | 2 | > | 3 | > --- > > - > |IND|KEY| VALUE | (T2) > - > | 1 | 1 | val_a | > | 1 | 2 | val_b | > | 1 | 3 | val_c | > | 2 | 1 | val_d | > | 2 | 2 | val_e | > | 3 | 1 | val_f | > | 3 | 3 | val_g | > - > > -- > |T1_INDEX|KEY1VAL|KEY2VAL|KEY3VAL| (T3) > -- > | 1| val_a | val_b | val_c | > | 2| val_d | val_e | | > | 3| val_f | | val_g | > -- > I think what you are looking for is cross tabulation, TRANSFORM statement, but I don't know if that is supported by PostgreSQL. Volker Paul
