Re: [SQL] How to alias table columns in result?

[nori]

Thanks

Sorry, my question was missing one important detail. My tables have
quite a lot columns (which unfortunately have same names in both
tables) so is it possible to do same as below but without specifying
alias for each column. Now my queries are long and they do not look
nice.

boris

On 8/11/05, Mischa Sandberg <[EMAIL PROTECTED]> wrote:
> SELECT d.name as "d.name",
>d.index as "d.index",
>c.name as "c.name",
>...

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] How to alias table columns in result?

[Nick Stone]

Yes - just alias the columns you need to alias

Nick 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of nori
Sent: 11 August 2005 10:48
To: Mischa Sandberg
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] How to alias table columns in result?

Thanks

Sorry, my question was missing one important detail. My tables have quite a
lot columns (which unfortunately have same names in both
tables) so is it possible to do same as below but without specifying alias
for each column. Now my queries are long and they do not look nice.

boris

On 8/11/05, Mischa Sandberg <[EMAIL PROTECTED]> wrote:
> SELECT d.name as "d.name",
>d.index as "d.index",
>c.name as "c.name",
>...

---(end of broadcast)---
TIP 6: explain analyze is your friend




---(end of broadcast)---
TIP 1: 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

Re: [SQL] How to alias table columns in result?

[nori]

Ok I got it. There is no way to alias all columns of some table with
some "prefix" that will be visible in result except to alias each
column.

Tnx Nick, Micsha
P.S. sorry for my bad english

On 8/11/05, Nick Stone <[EMAIL PROTECTED]> wrote:
> Yes - just alias the columns you need to alias
> 
> Nick
>

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] How to alias table columns in result?

[Jeff Boes]

nori wrote:
> Ok I got it. There is no way to alias all columns of some table with
> some "prefix" that will be visible in result except to alias each
> column.

Only other way would be to write a view for each table, then write all
your queries against the views.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[SQL] Fwd: How to encrypt a column

[Jeff Lu]

Hi,
 
I'm interested in encrypting an column in table.  Are there any example using "C" to create the encrypted column, inserting and retreiving data  to/from it?
 
the table is:
CREATE TABLE mytable (  id SERIAL PRIMARY KEY,  crypted_content BYTEA ); 
I'm getting (null) in the field with the following sql statement:
 
strcpy(data, "data to be encrypted");
sprintf(query_buff, "insert into mytable values('%s', '%s')", key, encrypt(data, 'foo', 'bar'));
PQexec(conn, query_string);
 
Another question is can the encrypted column be of type "text" ?
 
Thank you
		 Start your day with Yahoo! - make it your home page 

Re: [SQL] Fwd: How to encrypt a column

[Owen Jacobson]

Jeff Lu wrote:

> Hi,
>
> I'm interested in encrypting an column in table.  Are there any
> example using "C" to create the encrypted column, inserting and
> retreiving data  to/from it?
>
> the table is:
> CREATE TABLE mytable (
>  id SERIAL PRIMARY KEY,
>  crypted_content BYTEA
> );
>
> I'm getting (null) in the field with the following sql statement:
>
> strcpy(data, "data to be encrypted");
> sprintf(query_buff, "insert into mytable values('%s', '%s')", key,

Try printing out the contents of 'query_buff' here.  You shouldn't be
getting NULLs in the table, but you may well be getting '' (the empty
string) if encrypt(data, 'foo', 'bar') returns "" or (char *) (0).

> encrypt(data, 'foo', 'bar'));
> PQexec(conn, query_string);

Even better, don't do it this way.  This is potentially open to SQL
injection attacks and definitely open to problems with "interesting" values
of key.

Look up PQprepare/PQexecParams for separating the query string from the
values.  I use ODBC indirectly, so I can't help you directly, but the docs
at

should help.

> Another question is can the encrypted column be of type "text" ?

Can't see any reason why not, so long as the encrypted data is represented
as text.


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] Fwd: How to encrypt a column

[Hélder M . Vieira]

I'm interested in encrypting an column in table.  Are there any example 
using "C" to create the encrypted column, inserting and retreiving data 
to/from it?

the table is:
CREATE TABLE mytable (
id SERIAL PRIMARY KEY,
crypted_content BYTEA );
I'm getting (null) in the field with the following sql statement:
strcpy(data, "data to be encrypted");
sprintf(query_buff, "insert into mytable values('%s', '%s')", key, 
encrypt(data, 'foo', 'bar'));

PQexec(conn, query_string);



Another question is can the encrypted column be of type "text" ?



I'd check a previous critical point ... The encrypt() function output.
What is the range of characters in encrypt() output ?
Control, null or false escape characters, if not properly escaped, could be 
misleading either sprintf or the query processor in pg.



Helder M. Vieira





---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] Fwd: How to encrypt a column

[Jeff Lu]

I do get a couple of warnings at compile time:
intrasend.c:496:37: warning: multi-character character constantintrasend.c:496:44: warning: multi-character character constantintrasend.c:517:84: warning: character constant too long for its type
 
Here's what is in the variable "data" = "0018`0018`64045`08112005`64045`1Discount=0.00;``2``~"
 
printf("%s", encrypt(data, "foo", "bar")) 
prints out (null)
Thank youOwen Jacobson <[EMAIL PROTECTED]> wrote:
Jeff Lu wrote:> Hi,>> I'm interested in encrypting an column in table. Are there any> example using "C" to create the encrypted column, inserting and> retreiving data to/from it?>> the table is:> CREATE TABLE mytable (> id SERIAL PRIMARY KEY,> crypted_content BYTEA> );>> I'm getting (null) in the field with the following sql statement:>> strcpy(data, "data to be encrypted");> sprintf(query_buff, "insert into mytable values('%s', '%s')", key,Try printing out the contents of 'query_buff' here. You shouldn't begetting NULLs in the table, but you may well be getting '' (the emptystring) if encrypt(data, 'foo', 'bar') returns "" or (char *) (0).> encrypt(data, 'foo', 'bar'));> PQexec(conn, query_string);Even better, don't do i
 t this
 way. This is potentially open to SQLinjection attacks and definitely open to problems with "interesting" valuesof key.Look up PQprepare/PQexecParams for separating the query string from thevalues. I use ODBC indirectly, so I can't help you directly, but the docsatIN>should help.> Another question is can the encrypted column be of type "text" ?Can't see any reason why not, so long as the encrypted data is representedas text.---(end of broadcast)---TIP 5: don't forget to increase your free space map settings
		 Start your day with Yahoo! - make it your home page 

Re: [SQL] Fwd: How to encrypt a column

[Owen Jacobson]

Jeff Lu wrote:
> Owen Jacobson <[EMAIL PROTECTED]> wrote:
>> Jeff Lu wrote:
>>
>>> Hi,
>>>
>>> I'm interested in encrypting an column in table. Are there any
>>> example using "C" to create the encrypted column, inserting and
>>> retreiving data to/from it?
>>>
>>> the table is:
>>> CREATE TABLE mytable (
>>> id SERIAL PRIMARY KEY,
>>> crypted_content BYTEA

Consider making this column NOT NULL (crypted_content BYTEA NOT NULL).

>>> );
>>>
>>> I'm getting (null) in the field with the following sql statement:

See below: you're getting the string '(null)' and not a NULL string.

>>> strcpy(data, "data to be encrypted");
>>> sprintf(query_buff, "insert into mytable values('%s', '%s')", key,
>>
>> Try printing out the contents of 'query_buff' here. You shouldn't be
>> getting NULLs in the table, but you may well be getting '' (the
>> empty string) if encrypt(data, 'foo', 'bar') returns "" or (char *)
>> (0).
>>
>>> encrypt(data, 'foo', 'bar'));
>>> PQexec(conn, query_string);
>
> I do get a couple of warnings at compile time:
> intrasend.c:496:37: warning: multi-character character constant
> intrasend.c:496:44: warning: multi-character character constant
> intrasend.c:517:84: warning: character constant too long for its type

Somewhere in your code you've used '' instead of "" to delimit a string
literal.  Those line number and column number references will tell you
where.  The code you originally posted used the ' ' characters for 'foo' and
'bar', for instance.

(Yeah, it's inconvenient using different quoting conventions in the same
program.)

> Here's what is in the variable
> "data" = "0018`0018`64045`08112005`64045`1Discount=0.00;``2``~"
>
> printf("%s", encrypt(data, "foo", "bar"))
> prints out (null)

Well, there's the problem.  The encrypt function is returning the string
"(null)", which sprintf is (correctly) inserting into your query, and which
postgresql is then (correctly) interpreting as the literal string '(null)'
when storing the data.

Your problem is either in the encrypt() function you've written or in the
way you're using it.


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Fwd: How to encrypt a column

[Bruno Wolff III]

On Thu, Aug 11, 2005 at 13:50:45 -0700,
  Owen Jacobson <[EMAIL PROTECTED]> wrote:
> Jeff Lu wrote:
> 
> > Another question is can the encrypted column be of type "text" ?
> 
> Can't see any reason why not, so long as the encrypted data is represented
> as text.

There can't be any 0 bytes in the encrypted string or the string will be
truncated.

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[SQL] about subselect

[Louise Catherine]

Hi,
I make a subquery test at postgresql
7.3.3 and postgresql 8.0.3.
I found that postgresql 8.0.3 can't do this,
while at postgresql 7.0.3 it works quite well :
create table temp_hasil2(
nojob numeric,
nik numeric,
tanggal numeric,
kdpt numeric,
kdljr numeric
);

create table ap012(
nojob numeric,
tglavd numeric,
ptcd numeric
);

create table am382(
nojob numeric,
nik numeric,
tglavd numeric,
lkrjacd numeric
);

Update Temp_hasil2 Set KdPT = A.PTCD,
KdLjr = A.LKRJACD
>From (Select A38.NIK,A01.ptcd,A38.Lkrjacd
  From AM382 A38
   inner join
   ap012 A01 on A38.nojob = A01.nojob
  Where A38.tglavd =
(select max(B38.tglavd) from am382 B38
where A38.nik = B38.nik
and B38.tglavd <= Temp_hasil2.Tanggal)
And A01.tglavd =
(select max(B01.tglavd) from ap012 B01
where A01.nojob = B01.nojob
and B01.tglavd <= A38.tglavd)
 )A
Where Temp_hasil2.NIK = A.NIK;

If I run this query at Postgresql 8.0.3 :
Error: ERROR: subquery in FROM may
not refer to other relations of same query level.

Can someone help me solve this?,
because I've a plan to migrate database
from postgresql 7.3.3 to postgresql 8.0.3,
and I have a lot of case like one above
at my postgresql 7.3.3 databases.

Thanks,
Louise Catherine
NB: Sorry for my poor English




Start your day with Yahoo! - make it your home page 
http://www.yahoo.com/r/hs 
 

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] about subselect

[Tom Lane]

Louise Catherine <[EMAIL PROTECTED]> writes:
> I make a subquery test at postgresql
> 7.3.3 and postgresql 8.0.3.
> I found that postgresql 8.0.3 can't do this,
> while at postgresql 7.0.3 it works quite well :

7.3 says this:
NOTICE:  Adding missing FROM-clause entry in subquery for table "temp_hasil2"

which means that the query is invalid according to the SQL spec.  Later
versions are just being tougher about enforcing the spec.  If you are
happy with 7.3's interpretation of what the query means, try adding

> Update Temp_hasil2 Set KdPT = A.PTCD,
> KdLjr = A.LKRJACD
> From (Select A38.NIK,A01.ptcd,A38.Lkrjacd
>   From AM382 A38
>inner join
>ap012 A01 on A38.nojob = A01.nojob
>   Where A38.tglavd =
> (select max(B38.tglavd) from am382 B38, Temp_hasil2
^
> where A38.nik = B38.nik
> and B38.tglavd <= Temp_hasil2.Tanggal)
> And A01.tglavd =
> (select max(B01.tglavd) from ap012 B01
> where A01.nojob = B01.nojob
> and B01.tglavd <= A38.tglavd)
>  )A
> Where Temp_hasil2.NIK = A.NIK;

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] about subselect

[Louise Catherine]

I guess there's no other way,except adding the table
name one by one.

Thank you very much,
Louise Catherine.

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: **SPAM** [SQL] Faster count(*)?

[Keith Worthington]

Tom Lane wrote:

[EMAIL PROTECTED] writes:


I believe running count(*) means fulltable scan, and there's no way
to do it without it. But what about some "intermediate" table, with
the necessary counts?



There's a fairly complete discussion in the PG list archives of a
reasonably-efficient scheme for maintaining such counts via triggers.
It wasn't efficient enough that we were willing to impose the overhead
on every application ... but if you really NEED a fast count(*) you
could implement it.  I'd like to see someone actually do it and put
up working code on pgfoundry; AFAIK it's only a paper design so far.


I was kicking this around and came up with the following.  I have hit a 
couple of snags.


In the function I attempt to count the number of rows in a table being 
checked for the first time.  I wanted to use 'FROM TG_RELNAME' but as 
you can see I had to hard code my test table and comment out the trigger 
parameter. FROM tbl_demo--TG_RELNAME  Can someone tell me why that won't 
work?


Also the function doesn't seem to be getting ROW_COUNT properly.  The 
end result is that for this test the table is properly inserted into the 
monitoring table after it's forth insert but it is never updated after 
that.  Can someone help me see the forest through the trees?


-- Clean up the environment.
DROP TABLE tbl_row_count;
DROP TABLE tbl_demo;
DROP FUNCTION tf_update_row_count();

-- Build the table for holding the row counts.
CREATE TABLE tbl_row_count
(
  relid oid NOT NULL,
  row_count int8 NOT NULL DEFAULT 0
)
WITHOUT OIDS;
ALTER TABLE tbl_row_count OWNER TO postgres;
COMMENT ON COLUMN tbl_row_count.relid IS 'Contains relation id number.';
COMMENT ON COLUMN tbl_row_count.row_count IS 'Contains the number of 
rows in a relation.';


-- Build a table to test the trigger on.
CREATE TABLE tbl_demo
(
  first_name varchar(30) NOT NULL
)
WITHOUT OIDS;
ALTER TABLE tbl_demo OWNER TO postgres;
COMMENT ON TABLE tbl_demo IS 'Table used for demonstrating a trigger.';

-- Create the trigger function to maintain the row counts.
CREATE OR REPLACE FUNCTION public.tf_update_row_count()
  RETURNS "trigger" AS
$BODY$
   DECLARE
  v_row_count int8;
   BEGIN
--Store the row count before it disappears.
  GET DIAGNOSTICS v_row_count = ROW_COUNT;
--Check if this is a new table.
  PERFORM relid
 FROM public.tbl_row_count
WHERE relid = TG_RELID;
  IF FOUND THEN
--   Data for this table is already in the row count table.
 IF TG_OP = 'INSERT' THEN
UPDATE public.tbl_row_count
   SET row_count = row_count + v_row_count
 WHERE relid = TG_RELID;
 ELSIF TG_OP = 'DELETE' THEN
UPDATE public.tbl_row_count
   SET row_count = row_count - v_row_count
 WHERE relid = TG_RELID;
 END IF;
  ELSE
--   This is a new table so it needs to be counted.
 SELECT count(*)
   FROM tbl_demo--TG_RELNAME
   INTO v_row_count;
 INSERT INTO public.tbl_row_count ( relid, row_count )
VALUES ( TG_RELID,
 v_row_count
   );
  END IF;
  RETURN NULL;
   END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION public.tf_update_row_count() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION public.tf_update_row_count() TO postgres;
GRANT EXECUTE ON FUNCTION public.tf_update_row_count() TO public;

-- Insert some initial data into the demo table.
INSERT INTO public.tbl_demo
( first_name )
VALUES ( 'Keith' );
INSERT INTO public.tbl_demo
( first_name )
VALUES ( 'Ed' );
INSERT INTO public.tbl_demo
( first_name )
VALUES ( 'Kryss' );

-- Create the trigger on the demo table.
CREATE TRIGGER tgr_update_row_count
  AFTER INSERT OR DELETE
  ON public.tbl_demo
  FOR EACH STATEMENT
  EXECUTE PROCEDURE public.tf_update_row_count();

-- Examine the starting state of the tables.
SELECT *
  FROM public.tbl_demo;
SELECT *
  FROM public.tbl_row_count;
SELECT relid,
   relname,
   row_count
  FROM public.tbl_row_count
  LEFT JOIN pg_class
ON ( tbl_row_count.relid = pg_class.oid );

-- Insert a row.
INSERT INTO public.tbl_demo
( first_name )
VALUES ( 'Jarus' );

-- Examine the new state of the tables.
SELECT *
  FROM public.tbl_demo;
SELECT relid,
   relname,
   row_count
  FROM public.tbl_row_count
  LEFT JOIN pg_class
ON ( tbl_row_count.relid = pg_class.oid );

-- Insert two more rows.
INSERT INTO public.tbl_demo
( first_name )
VALUES ( 'Dani' );
INSERT INTO public.tbl_demo
( first_name )
VALUES ( 'Mary' );

-- Examine the final state of the tables.
SELECT *
  FROM public.tbl_demo;
SELECT relid,
   relname,
   row_count
  FROM public.tbl_row_count
  LEFT JOIN pg_class
ON ( tbl_row_count.relid = pg_class.oid );

--
Kind Regards,
Keith

---(end of bro