WITH ENCRYPTION feature ?

PG 8.2
Red Hat Linux

Hello -

When I execute the following query,
select A.domain_name_dom, A.word_wdl, A.count_ofi, A.total_quality_ofi,
A.avg_quality_ofi
from zzz_brand2domain_step2_bs2 as A join
(select B.domain_ofi, B.word_ofi, B.count_ofi, B.total_quality_ofi,
B.avg_quality_ofi
from offpagedomainwordintersection_ofi as B
where B.domain_ofi != A.domain_name_dom
order by B.total_quality_ofi desc
limit 1) as C
on A.word_wdl = B.word_ofi;

I receive this error message
ERROR: invalid reference to FROM-clause entry for table "a"
LINE 5: where B.domain_ofi != A.domain_name_dom
^
HINT: There is an entry for table "a", but it cannot be referenced from
this part of the query.

And when I execute this query
select domain_name_dom, word_wdl, count_ofi, total_quality_ofi,
avg_quality_ofi
from zzz_brand2domain_step1_bs1 as B join
(select * from offpagedomainwordintersection_ofi
where B.domain_name_dom = domain_ofi
and B.word_wdl = word_ofi
and total_quality_ofi >= 5900) as A
on (domain_name_dom = A.domain_ofi
and word_wdl = A.word_ofi)
limit 10;

I receive this error message
ERROR: invalid reference to FROM-clause entry for table "b"
LINE 4: where B.domain_name_dom = domain_ofi
^
HINT: There is an entry for table "b", but it cannot be referenced from
this part of the query.

I don't understand why I can't reference the respective tables in the
queries. Any assistance would be greatly appreciated.

Thank you.

David

"David Monarchi" <david(dot)e(dot)monarchi(at)gmail(dot)com> writes:
> When I execute the following query,
> select A.domain_name_dom, A.word_wdl, A.count_ofi, A.total_quality_ofi,
> A.avg_quality_ofi
> from zzz_brand2domain_step2_bs2 as A join
> (select B.domain_ofi, B.word_ofi, B.count_ofi, B.total_quality_ofi,
> B.avg_quality_ofi
> from offpagedomainwordintersection_ofi as B
> where B.domain_ofi != A.domain_name_dom
> order by B.total_quality_ofi desc
> limit 1) as C
> on A.word_wdl = B.word_ofi;

> I receive this error message
> ERROR: invalid reference to FROM-clause entry for table "a"
> LINE 5: where B.domain_ofi != A.domain_name_dom
> ^
> HINT: There is an entry for table "a", but it cannot be referenced from
> this part of the query.

> I don't understand why I can't reference the respective tables in the
> queries.

Because a JOIN happens between two *independent* tables. The above
isn't well-defined.

regards, tom lane

Hi Everyone -

I'm looking for a SQL statement that I can use to find out if a given
column in a table uses a specific sequence as it's next default value.
Is this possible?

For example, if the table was created with:

CREATE TABLE accounts
(
account_id integer NOT NULL DEFAULT
nextval(('accounts_account_id_seq'::text)::regclass),
account_number character varying(40) NOT NULL
)

Is there a way for me to query the database metadata to verify that
accounts.account_id uses the sequence accounts_account_id_seq as it's
default next value?

Thanks in advance!

Scott.

"Scott Ford" <Scott(dot)Ford(at)bullfrogpower(dot)com> writes:
> CREATE TABLE accounts
> (
> account_id integer NOT NULL DEFAULT
> nextval(('accounts_account_id_seq'::text)::regclass),
> account_number character varying(40) NOT NULL
> )

> Is there a way for me to query the database metadata to verify that
> accounts.account_id uses the sequence accounts_account_id_seq as it's
> default next value?

If it's done as above (with a run-time cast from text to regclass),
there isn't any hardwired connection between the column and the sequence
--- for all the database knows, that string value might be different
each time. (On a more practical note, renaming the sequence or changing
the schema search path could change which sequence gets used.) So about
all you could do is look at pg_attrdef.adsrc and try to extract the
string as a string. Messy, and I don't recommend it.

The more modern way to represent a serial default is

DEFAULT nextval('accounts_account_id_seq'::regclass)

which is different because a regclass constant is actually a reference to
the OID of the sequence. It will track renamings of the sequence (even
across schemas), and more to the point for the immediate purpose, the
database "knows" that this is a reference to the sequence --- for
instance it won't let you drop the sequence without removing the default
expression. The way it knows that is that there's an entry in pg_depend
linking the sequence to the default expression.

So the bottom line here is that you can find out the connection by
joining pg_attrdef to pg_depend to pg_class. I'm too lazy to present a
worked-out example, but you can probably find something related in the
source code for pg_dump.

BTW, if you actually declare the column as a serial column, you could
just use pg_get_serial_sequence() for this. That also does a pg_depend
join under the hood, but it's a little different from the one you'd need
to find a column that has a handmade default referencing a sequence.

regards, tom lane

Hi,
Is there a translation of this MSSQL feature in PostgreSQL? It's
still actually possible to decrypt the encrypted stored procedure in the
server but end of the day it should be recognized that all these efforts
will just serve to make things slightly more difficult (e.g. leaving
your car unlocked instead of locked or leaving the keys inside). Anyways
the feature in MSSQL will encrypt the stored procedure in the server
such that when it's viewed by a sysad or anybody else, they will
encrypted text instead of T-SQL code. Thanks in advance.

Hi,
Is there a translation of this MSSQL feature in PostgreSQL? It's
still actually possible to decrypt the encrypted stored procedure in the
server but end of the day it should be recognized that all these efforts
will just serve to make things slightly more difficult (e.g. leaving
your car unlocked instead of locked or leaving the keys inside). Anyways
the feature in MSSQL will encrypt the stored procedure in the server
such that when it's viewed by a sysad or anybody else, they will
encrypted text instead of T-SQL code. Many Thanks in advance for any
clues.

On Jan 24, 2008 3:39 AM, Robert Bernabe <rbernabe(at)sandmansystems(dot)com> wrote:

> Hi,
> Is there a translation of this MSSQL feature in PostgreSQL? It's
> still actually possible to decrypt the encrypted stored procedure in the
> server but end of the day it should be recognized that all these efforts
> will just serve to make things slightly more difficult (e.g. leaving
> your car unlocked instead of locked or leaving the keys inside). Anyways
> the feature in MSSQL will encrypt the stored procedure in the server
> such that when it's viewed by a sysad or anybody else, they will
> encrypted text instead of T-SQL code. Thanks in advance.

I don't think so.

Sean

On Jan 24, 2008 4:32 AM, Robert Bernabe <rbernabe(at)sandmansystems(dot)com> wrote:
> Hi,
> Is there a translation of this MSSQL feature in PostgreSQL? It's
> still actually possible to decrypt the encrypted stored procedure in the
> server but end of the day it should be recognized that all these efforts
> will just serve to make things slightly more difficult (e.g. leaving
> your car unlocked instead of locked or leaving the keys inside). Anyways
> the feature in MSSQL will encrypt the stored procedure in the server
> such that when it's viewed by a sysad or anybody else, they will
> encrypted text instead of T-SQL code. Many Thanks in advance for any
> clues.

Nope, there is no such way to do this in Postgres. You can look
through the archives (primarily on -hackers) regarding encryption,
wrapping, and obfuscation.

--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah(dot)harris(at)enterprisedb(dot)com
Edison, NJ 08837 | http://www.enterprisedb.com/