Обсуждение: SQL state: 42P01
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
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.e.monarchi@gmail.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.Ford@bullfrogpower.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@sandmansystems.com> wrote:
I don't think so.
Sean
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@sandmansystems.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.harris@enterprisedb.com Edison, NJ 08837 | http://www.enterprisedb.com/